Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.