Power BI, SSAS Multidimensional And Dynamic Format Strings

If you’re building reports in Power BI against SSAS Multidimensional cubes then you may have encountered situations where the formatting on your measures disappears. For example, take a very simple SSAS Multidimensional cube with a single measure called Sales Amount whose FormatString property is set in SSDT to display values with a £ sign:

image

When you build a report using the Table visualisation in Power BI Desktop using this measure, the formatted values are displayed correctly:

image

However, if you add a SCOPE statement to the cube to alter the format string of the measure for certain cells, as in this example which sets the format string for the Sales Amount measure to $ for Bikes:

SCOPE([Measures].[Sales Amount], [Product].[Category].&[1]);
    FORMAT_STRING(THIS)="$0,0.00";
END SCOPE;

…then you’ll find that Power BI displays no formatting at all for the measure:

image

What’s more (and this is a bit strange) if you look at the DAX queries that are generated by Power BI to get data from the cube, they now request a new column to get the format string for the measure even though that format string isn’t used. Since it increases the amount of data returned by the query much larger, this extra column can have a negative impact on query performance if you’re bringing back large amounts of data.

There is no way of avoiding this problem at the moment, unfortunately. If you need to display formatted values in Power BI you will have to create a calculated measure that returns the value of your original measure, set the format string property on that calculated measure appropriately, and use that calculated measure in your Power BI reports instead:

SCOPE([Measures].[Sales Amount], [Product].[Category].&[1]);
    FORMAT_STRING(THIS)="$0,0.00";
END SCOPE;

CREATE MEMBER CURRENTCUBE.[Measures].[Test] AS 
[Measures].[Sales Amount],
FORMAT_STRING="£0,0.00";

image

Thanks to Kevin Jourdain for bringing this to my attention and telling me about the workaround, and also to Greg Galloway for confirming the workaround and providing extra details.

13 thoughts on “Power BI, SSAS Multidimensional And Dynamic Format Strings

  1. The disregard for SSAS format strings by client tools has been a frustrating issue for years, in my experience. Microsoft tools are the major offender. Setting the format strings in SSAS makes for a centralized, more easily manageable repository for client consumption. I have spent days ensuring consistent format strings in large collections of calculated measures, only to have to repeat the work by building template reports in the client tools, or by issuing documentation to users in the hopes that they will properly format measures in the reports they create.

      • The point is that some formatting might be better than no formatting at all, and a calculated measure is the only way to get some formatting to work. Sorry not to be clear.

      • think the point is that you may still have different client reporting tools (e.g. Excel, SSRS) that can make use of the scope assignment formatting logic…

  2. OK thanks. It really annoys me that Power BI doesn’t support some SSAS MD properties really well. Luckily the client is also a heavy Excel user, so the formatting is not lost everywhere.

  3. I have to say, the fact that you can’t format a number in Power BI makes it useless to me. I prefer writing as much as I can in SQL and use Power Query, where I can format the number to my heart’s delight in Excel. Creating a measure to format a number makes no sense! I don’t understand why they don’t add that option to the column/report itself. Seriously aggravating.

  4. I’m not seeing any number format in Power BI. Not on measures or calculated measures (scoped or not). Do we have to be on a specific version of SQL for Power BI to honor it? We are running SSAS 2012 SP3

      • Thanks for your answer Chris. I will try to create a very simple cube to test this. I have tried several of our cubes without success so that’s why I was thinking it might be the server version.

        Again thanks for your answer

      • I actually works with a simple cube. And I found couple of our cubes that it works with as well. Now I just need to find out what it is with our more complex cubes that prevents it for working. Do you know if there is any documentation from Microsoft on this?

  5. Hi Chris,

    Thanks for posting on this important issue. I still am not able to solve this problem. I have some calculated member and then used in the YTD timecalculations with base measures. Please see the code below.

    [Measures].[Actual] is a base measure with format_String = “#,##0;-#,##0”

    //calculated measures
    CREATE MEMBER CURRENTCUBE.[Measures].[Budget]
    AS SUM([BUDGETVERSION].[VERSION].[Budget],[Measures].[BUDGET VALUE]),
    FORMAT_STRING = “#,##0;-#,##0”,
    VISIBLE = 1 ;

    CREATE MEMBER CURRENTCUBE.[Measures].[Actual-Budget]
    AS [Measures].[Actual]-[Measures].[Budget],
    FORMAT_STRING = “#,##0;-#,##0”,
    VISIBLE = 1;

    Create Member
    CurrentCube.[DATE].[Time Calculations].[Year to Date]
    As “NA”;

    ( {
    [Measures].[Actual],
    [Measures].[Budget],
    [Measures].[Actual-Budget]
    },
    (
    [DATE].[Time Calculations].[Year to Date],
    [DATE].[YEAR].[YEAR].Members,
    [DATE].[DATE].Members
    )
    ) =SUM(
    { [DATE].[Time Calculations].[Current Period] }
    *
    PeriodsToDate(
    [DATE].[CalendarDate].[YEAR],
    [DATE].[CalendarDate].CurrentMember
    )
    );

    This code is in production for over a year and was working in PowerBI very good. Now for somedays both percentage formatting and decimal formatting is gone. Your workaround of creating another calculated member i cannot get it for Time calculations working. Do you have any idea on how to get this working. Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s