Dynamic DAX Measure Not Showing In Charts

[SQL_McOLAP]

Hi -

Our end users really like an ad-hoc screen we set up where they can choose a bunch of measures they want to analyze.  Without formatting, those measures could get hard to read, especially when there are a lot of currency, unit count and ratio measures coming from our SSAS tabular cube (direct connect).

We did this by having some dummy dim tables in the tabular cube that just selected the names of measures in our cube, and then creating new measures in the Power BI front end, like:

Measure1 = SWITCH(ALLSELECTED('Select Ad Hoc Measure 1'[Ad Hoc Measure 1]),

"One Of Our Ratio Measures",DIVIDE([One Of Our Currency Measures],[Another One Of Our Currency Measures]),
"One Of Our Currency Measures",SUM([Currnecy Col From Our Cube]),
"One Of Our DISTINCT COUNT Measures",CALCULATE(DISTINCTCOUNT([A Specific ID Col From Our Cube]))

,0)

So this is all working well in both table/matrix visualizations and charts, but again, the lack of formatting makes the values difficult to read.

When I changed the above Measure1 DAX to:

Measure1 = SWITCH(ALLSELECTED('Select Ad Hoc Measure 1'[Ad Hoc Measure 1]),

"One Of Our Ratio Measures",Format(DIVIDE([One Of Our Currency Measures],[Another One Of Our Currency Measures]),"%.00"),
"One Of Our Currency Measures",Format(SUM([Currnecy Col From Our Cube]),"$0,00.00"),
"One Of Our DISTINCT COUNT Measures",Format(CALCULATE(DISTINCTCOUNT([A Specific ID Col From Our Cube])),"0,0")

,0)

...the numbers look great in the table/matrix visualizations, but they vanish from the charts.  I'm sure because after they've been formatted they're considered strings instead of numerics as a reason for this happening, but can anyone think of a workaround where we could keep the numerics from the first calc for the charts, and the formatted one for the tables/matrices and tooltips?

Thanks!

-Phil (SQL McOLAP)

[SQL_McOLAP · Accepted solution]

So I think the answer to my question is to have TWO measures.  One with the SWITCH that calcs the ad hoc measure the user selected, and another measure that does the same but has the FORMAT function around all of them.  The first one would be used for charting, and the second one, with the FORMAT function would be for tables/matrices and Tooltips, for user-readability.

If someone knows of a way to do this WITHOUT creating a second measure, let me know, becuase we have over 175 measures in our cube, and the DAX to do all this is pretty lengthy.

Thanks!

-Phil (SQL McOLAP)

[v-yulgu-msft]

Hi @SQL_McOLAP,

Thanks for your sharing resolution.

Regards,

Yuliana Gu