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:

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

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:

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

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:

18 responses

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.

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.

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

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?

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”

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!

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.

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 ).

I was testing in Power BI today and while the percentage formatting came through in the table visualization, when I tried a chart the values went to decimals with no way to format the value or axis as percentage. PowerBI with Mulidimensional is a letdown. Do you think the MS response would be “rewrite your cubes in tabular”?

Follow Blog via Email

Social

Need some help?

As well as being a blogger, I'm an independent consultant specialising in Analysis Services, MDX, DAX, Power BI, Power Query and Power Pivot. I work with customers from all round the world solving design problems, performance tuning queries and delivering training courses, and I am happy to work on short-term engagements. For more details see http://www.crossjoin.co.uk