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.

UPDATE October 2017: this issue appears to be fixed in the latest release of Power BI https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-october-2017-feature-summary/#analytics

16 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!

  6. Hi Chris,

    I have different question and it is regarding to Scope statement

    In MDX, I have one scenario , I have three dimensions and one fact table
    DimCost Centre  Cost Centre, Cost Centre Name,
    DimJob  Job No, Job Name
    DimCandidate  Candidate Id, Candidate Name
    FactJob Transactions No of Positions, No of Candidate
    Created a measure No of Positions and No of Candidate and basis aggregation is Max
    But when it comes with cost centre, it should return the values sum of max (No of Positions) .i.e. Sum(Max(No of Positions)) or Sum(Max(No of Candidate)).
    Cost Center Job No Candidate ID No of Positions
    C1 Job 1 Candidate 1 1
    C1 Job 1 Candidate 2 1
    C1 Job 2 Candidate 3 2
    C2 Job 2 Candidate 4 2
    C2 Job 3 Candidate 5 3

    When the data slices, for Job or Candidate Id attributes the No of Positions should be 1 for Job 1 and 2 for job 2 and 3 for Job 3 but for the cost centre attributes the No of Positions should be 1+2 (C1) and (2+3) for C2.
    At the job and candidate level , cube is returning the max of No of positions and it is correct but it is also giving Max of No of positions at the cost centre level but I need the sum of max of job
    Here is the scope statement I wrote
    SCOPE([Cost Centre].[Level5].[Level5], [Measures].[ No of positions]);
    THiS= SUM([Measures].[ No of positions]);
    End Scope
    I also tried another scope statement but it never return the result and it is going on
    SCOPE([Cost Centre].[Level5].[Level5], [Measures].[ No of positions]);
    THiS= SUM(([Cost Centre].[Level5].[Level5], [Job].[Job Id].[Job Id]) [Measures].[ No of positions]);
    End Scope
    I don’t know where am I making mistake.

  7. Hi Chris,

    Thanks for the reply. I read the blog you mentioned in your reply but i have different scenario.

    my scenario to create one measure and it behaves different at different dimension.
    Say my measure name is NoofPosition. The NoOfPosition should be the max value at Job dimension and at cost centre it should be Sum of Max(NoOfPosition ).

    Here is my code
    SCOPE( [Measures].[MaxNumber Of Positions] );

    THIS = SUM(Exists( [Cost Centre].[Level5].Currentmember), [Job Profile Erecruit].[Job Reference Number].[Job Reference Number]), [Measures].[MaxNumber Of Positions]));

    END Scope;

    The code does not work and when I browse the measure in excel and it is nonstop running and never give me the result.

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