Dynamically format measure from SSAS Tabular to Power BI

[Lotta]

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.

[v-cherch-msft]

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

[Lotta]

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

[v-cherch-msft]

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

[Lotta]

Hi @v-cherch-msft, 

It's a live connection.
Neither the percentage measure or elinimation is empty as they are shown in Excel and in the SQL SSAS Tabular browser.

It seem to be specific to Power BI?

Kind Regards 
Lotta

[v-cherch-msft · Accepted solution]

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

https://community.powerbi.com/t5/Desktop/Formatting-SSAS-values-Live-connection-explore-live/td-p/29...

Regards,

Cherie

[Lotta]

Hi @v-cherch-msft, 

Thanks - that is what I was afraid of.

I posted as a desperate last chance that Power BI should have same abilities as Excel as a minimum.

Kind Regards
Lotta