Forum Discussion
Conditionnal Number Format
Good evening,
I'm working on a dynamic column chart which displays a specific metric depending on the option selected in a separate slicer. Here is the measure used in the Y-Axis to get this result :
Y - Axis = VAR SelectedMeasure = SELECTEDVALUE('Measure Table'[Y-Axis]) RETURN SWITCH ( SelectedMeasure, "EV / EBITDA LTM", [EV / EBITDA (LTM)], "EV / EBITDA NTM", [EV / EBITDA (NTM)], "EV / Industry Metric", [EV / Industry Metric], "EV", [EV (in M$)], "Discount Rate", [Discount Rate (%)], BLANK () ).
The closer I got to achieving the result wanted was through the measure Y-Axis Spe and Formatted Y - Axis. The measures works on Tables but for some reason, it doesn't work on column charts.
How can I get a measure that can adjust the number format when a specific metric is selected in the slicer (in this case, the "discount rate") and that works on graph ?
I would really appreciate some help if anyone has a solution in mind.
Thank you very much!!
Anonymous I guess selectedvalue is the issue, read this post to get the selected value.
Anonymous this should be your measure (in my opinion)
Dynamic Title - progression annuelle = VAR SelectedValue1 = SELECTCOLUMNS ( SUMMARIZE ( Parameter, Parameter[Parameter], Parameter[Parameter Fields] ), Parameter[Parameter] ) VAR SelectedValue2 = SELECTEDVALUE ( 'Comparables Database - Power BI Dashboard'[Sector] ) RETURN SWITCH ( TRUE (), SelectedValue1 = "EV / Industry Metric", SWITCH ( TRUE (), SelectedValue2 = "Infrastructure", "EV / MW", SelectedValue2 = "Agriculture", "EV / Hectare", "EV / Industry Metric" ), SelectedValue1 = "Discount Rate", "Discount Rate", SelectedValue1 = "EV / EBITDA LTM", "EV / EBITDA LTM", SelectedValue1 = "EV / EBITDA NTM", "EV / EBITDA NTM", SelectedValue1 = "EV / Revenue", "EV / Revenue", SelectedValue1 = "EV", "EV (in M$)" )
13 Replies
Anonymous there are many ways to solve this:
first, you can use field parameters for the measure selection rather than using a disconnected table (I assume that is what you did)
2nd, you can use dynamic format feature to change the format of the measure based on the selection, check this video on dynamic format:
Thank you very much! I used the field parameters instead of my disconnected table and the number format is working perfectly. However, I got a new problem resulting from the change. The table of my graph was based on this measure :
I adjusted it to reflect the changes I made so it can refer to the parameter chosen :
But I'm getting this error after changing the title measure :
Do you know what may cause this error ?
Thank you very much for your help!
Anonymous I guess selectedvalue is the issue, read this post to get the selected value.
Anonymous this should be your measure (in my opinion)
Dynamic Title - progression annuelle = VAR SelectedValue1 = SELECTCOLUMNS ( SUMMARIZE ( Parameter, Parameter[Parameter], Parameter[Parameter Fields] ), Parameter[Parameter] ) VAR SelectedValue2 = SELECTEDVALUE ( 'Comparables Database - Power BI Dashboard'[Sector] ) RETURN SWITCH ( TRUE (), SelectedValue1 = "EV / Industry Metric", SWITCH ( TRUE (), SelectedValue2 = "Infrastructure", "EV / MW", SelectedValue2 = "Agriculture", "EV / Hectare", "EV / Industry Metric" ), SelectedValue1 = "Discount Rate", "Discount Rate", SelectedValue1 = "EV / EBITDA LTM", "EV / EBITDA LTM", SelectedValue1 = "EV / EBITDA NTM", "EV / EBITDA NTM", SelectedValue1 = "EV / Revenue", "EV / Revenue", SelectedValue1 = "EV", "EV (in M$)" )Anonymous can you click the details and provide the error message?
Anonymous can you paste the actual code instead of the screen shot?
- Dynamic Title - progression annuelle =VAR SelectedValue1 = SELECTCOLUMNS (SUMMARIZE ( Parameter, Parameter[Parameter], Parameter[Parameter Fields] ),Parameter[Parameter])RETURN IF ( COUNTROWS ( SelectedValue1 ) = 1, SelectedValue1 )VAR SelectedValue2 = SELECTEDVALUE('Comparables Database - Power BI Dashboard'[Sector])RETURNSWITCH (TRUE (),SelectedValue1 = "EV / Industry Metric",SWITCH (TRUE (),SelectedValue2 = "Infrastructure", "EV / MW",SelectedValue2 = "Agriculture", "EV / Hectare","EV / Industry Metric" ),SelectedValue1 = "Discount Rate", "Discount Rate",SelectedValue1 = "EV / EBITDA LTM", "EV / EBITDA LTM",SelectedValue1 = "EV / EBITDA NTM", "EV / EBITDA NTM",SelectedValue1 = "EV / Revenue", "EV / Revenue",SelectedValue1 = "EV", "EV (in M$)")
Anonymous why you have following line in your expression:
RETURN IF ( COUNTROWS ( SelectedValue1 ) = 1, SelectedValue1 )