cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lotta
Frequent Visitor

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

Power_BI_Desktop_Table_Matrix.pngExcel_Pivot_Table.jpg

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.

1 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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-cherch-msft
Microsoft
Microsoft

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

1.png

 

Regards,

Cherie

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

DataModel_ex.JPG

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

SSAST_Browse.JPG
It seem to be specific to Power BI?

 

Kind Regards 
Lotta

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.