Forum Discussion
Dynamically format measure from SSAS Tabular to Power BI
Hi,
I have tried various ways to format a measure dynamically from SSAS Tabular to Power BI Desktop.
In Excel all versions of my tries works like a charm.
But as soon as I connect to the cube (with the measure) in Power BI Desktop, it won't show anything.
I have tried both table and matrix in Power BI - same result = nothing.
Left pic = Power BI, Right pic = Excel Power Pivot
I want to have row labels on rows, but dependent of Row label I'll show different values, to get % values and elimination values in the same column.
It's not possible to make a measure for each row (there are a lot more than in the pictures) as it's not at all userfriendly.
I have tried...
Try 1:
=IF(IF(HASONEVALUE('Income Statement Lines'[Line]); VALUES('Statement Lines'[line]) = "Elimination"); [Elimination_];
IF(IF(HASONEVALUE('Statement Lines'[Line]); VALUES('Statement Lines'[line]) = "CB1 %");FORMAT(DIVIDE(Finance [CB1_]; Finance [Revenue_]; 0) * 100; "##0.0") & " %";
IF(IF(HASONEVALUE('Statement Lines'[Line]); VALUES('Statement Lines'[line]) ="EBIT %"); FORMAT(DIVIDE(Finance [EBIT_]; Finance [Revenue_]; 0) * 100; "##0.0") & " %";
[Report value] )))
Try 2:
=IF(FIRSTNONBLANK('Statement Lines'[Line];1) = "Elimination"; Finance[Elimination_];
IF(FIRSTNONBLANK('Statement Lines'[Line];1) = "CB1 %"; FORMAT(DIVIDE(Finance[CB1_]; Finance [Revenue_]; 0); "##0.0 %");
IF(FIRSTNONBLANK('Statement Lines'[Line];1) = "EBIT %"; FORMAT(DIVIDE(Finance [EBIT_]; Finance [Revenue_]; 0); "##0.0 %");
[Report value]
) ) )
Try 3:
=IF(LASTNONBLANK(‘Income Statement Lines’[Line];1) = “Elimination”; FinanceFacts[Elimination_];
IF(LASTNONBLANK(‘Income Statement Lines’[Line];1) = “CB1 %”; FORMAT(DIVIDE(FinanceFacts[CB1_]; FinanceFacts[Revenue_]; 0); “##0.0 %”);
IF(LASTNONBLANK(‘Income Statement Lines’[Line];1) = “EBIT %”; FORMAT(DIVIDE(FinanceFacts[EBIT_]; FinanceFacts[Revenue_]; 0); “##0.0 %”);
[Report value]
) ) )
Try 4:
=IF(HASONEVALUE('Statement Lines'[Line]);
SWITCH(
VALUES('Statement Lines'[Line]);
"Elimination"; Finance[Elimination_];
"CB1 %"; FORMAT(DIVIDE(Finance[CB1_]; Finance [Revenue_]; 0); "##0.0 %");
"EBIT %"; FORMAT(DIVIDE(Finance [EBIT_]; Finance [Revenue_]; 0); "##0.0 %");
[Report value]
);
[Report value]
)
Any help to get the euivalent formatting in Power BI as in Excel is greately appreciated.
Hi Lotta
Based on my research, it is not supported in SSAS live connection mode. Below are some similar posts for your reference.
https://community.powerbi.com/t5/Desktop/Data-type-of-calculated-measures-in-Import-Mode/td-p/143923
Regards,
Cherie
6 Replies
Hi Lotta
Based on my test as below picture, it seems the second measure is correct. Just try again with below measure. If it is not your case, please share more details about your data sample so that we could help further on it.
Try = IF ( MAX ( 'Statement Lines'[Line] ) = "Elimination", Finance[Elimination_], IF ( MAX ( 'Statement Lines'[Line] ) = "CB1 %", FORMAT ( DIVIDE ( Finance[CB1_], Finance[Revenue_], 0 ), "##0.0 %" ), IF ( MAX ( 'Statement Lines'[Line] ) = "EBIT %", FORMAT ( DIVIDE ( Finance[EBIT_], Finance[Revenue_], 0 ), "##0.0 %" ), [Report value] ) ) )Regards,
Cherie
Hi v-cherch-msft,
Thanks for the suggestion.
I can't get to work, though, as the MAX function does not take a string as argument.Statement_Lines[Line] are stings.
Below a part of the SSAS Tabular model. We are using SQL Server 2016, SP1.
The users select Stement_lines[Line] on row and the measure as value.
Child is also strings.This is an atempt to show report rows and the different values on the rows for the matching Statement_Lines[Line].
Kind regards
Lotta
Hi Lotta
Did you import the data? If so, could you share the sample file for us to check? MAX Function can be replaced with FIRSTNONBLANK Function as your second measure. Maybe you need check if measure= FORMAT(DIVIDE(FinanceFacts[EBIT_]; FinanceFacts[Revenue_]; 0); “##0.0 %”) is blank value,because in my test, the IF condition seems correct.
Regards,
Cherie
