SCOPE() format_string problem SSAS live

[kaarel]

Hi!

I think I found a bug. 

I've got 2 measures with 2 different format strings. "Margin" with #,##0 and "Margin SEK" with #,##0'kr'. The latter represents Swedish currency. 

In the script I switch between measures based on what currency is selected from the Currency dimension.

SCOPE
(
[Currency].[Currency].&[8],
[Measures].[Margin]
);

THIS=[Measures].[Margin SEK];

END SCOPE;

PowerBI doesn't show the format string correctly when "Margin SEK" is with #,##0'kr'. When I switch over to #,##0 for both measures, then it works. 

Thanks,

Kaarel.

[gfuribondo]

Yo.  Microsoft.  Any word on this?

[kaarel]

Maybe we could start a bug report or smt?

[sergezai]

I have a similar problem with scope() and formatting  values in PowerBI. I found that any scope statement in SSAS cube actually screws up the formatting of any measure defined before the scope statement. Microsoft confirmed that it's a bug in Power BI but they didn't provide any further details when it's gonna be fixed.

[S-Driancourt]

I had a simular issue, and thanks to your post I was able to solve my problem, by putting all the scopes before the creation of the calculated members.

[kaarel]

Chris Webb recently blogged about this:

https://blog.crossjoin.co.uk/2017/04/18/power-bi-ssas-multidimensional-and-dynamic-format-strings/

[kaarel]

Hopefully soon...

[kaarel]

And another example... I've got a calculation defined with a format string. Once I remove SCOPE() assignments, then PowerBI

shows the format string correctly. Otherwise it doesn't.

CREATE MEMBER CURRENTCUBE.[Measures].[Invoice Rowtotal - last year]
AS [Rowtotal LY],
FORMAT_STRING = "#,##0",
FORE_COLOR = 9079434 /*R=138, G=138, B=138*/ ,
VISIBLE = 1 , DISPLAY_FOLDER = 'Revenue\EUR' , ASSOCIATED_MEASURE_GROUP = 'Invoice Line';

SCOPE(DESCENDANTS([Date].[Year-Week-Date],,AFTER));
[Measures].[Invoice Rowtotal - last year] = SUM(EXISTING [Weeks],(PARALLELPERIOD([Date].[Year-Week-Date].[Year],1,[Date].[Year-Week-Date].CurrentMember),[Measures].[Invoice Rowtotal]));
END SCOPE;

SCOPE(DESCENDANTS([Date].[Year-Week-Date],,Leaves));
[Measures].[Invoice Rowtotal - last year] = null;
END SCOPE;

SCOPE(DESCENDANTS([Date].[Year-Quarter-Month-Date],,Leaves));
[Measures].[Invoice Rowtotal - last year] = null;
END SCOPE;