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:
[sourcecode language=”text” padlinenumbers=”true”]
SCOPE([Measures].[Sales Amount], [Product].[Category].&[1]);
FORMAT_STRING(THIS)="$0,0.00";
END SCOPE;
[/sourcecode]
…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:
[sourcecode language=”text”]
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";
[/sourcecode]
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
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.
Is it me, or does the dynamic formatting still not work? I don’t see any dollars in the last screenshot.
(which would make sense since the format string of the calculated measure is in pounds. So what is the point of all this?)
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…
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.
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 don’t know – I don’t think so. Maybe there is some other feature you are using in your cube that is stopping this working?
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?
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!
Unfortunately, as I say in the post, there is no workaround – dynamic number formats like this don’t work in Power BI. Sorry!
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.
I not sure I fully understand what you want to do, but have you read this post? https://blog.crossjoin.co.uk/2013/05/29/aggregating-the-result-of-an-mdx-calculation-using-scoped-assignments/
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.
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”?
This is still not fixed. Not sure why the Power BI team has not fixed this formatting issue with chart visualizations.
Hi all, still there are issues in formatting both in PowerBI Desktop and PowerBI Service.
1)If there are scopes statements with dynamic(this keyword) before calculated members, formats on all measures in charts are not displayed.
2)If there are scopes statements with dynamic(this keyword) before calculated members, scopes with a format specified cannot change the format of measures or calculated members in matrix. (For example using DateTool with Previous Variance %)
I’ve put this at the very end of my script, at it seems to hold for both Excel AND PowerBI:
// FINAL FORMATTING (for Power BI)
FORMAT_STRING([Measures].[some percentage],MEMBERS)= “Percent”;