Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a Power BI report with a measure that returns the value of other measures depending on the selected value of a slicer, as per the DAX below:
Slicer Measure =
SWITCH(
LASTNONBLANK('Available Measures'[MeasureName], "")
, "Sales", [Sales]
, "Orders", [Orders]
, "Orders Completed %", [Orders Completed %]
)
This allows a user to select which measure they want to see in a line chart visual.
However, when the user selects Orders Completed %, I want the [Slicer Measure] to be formatted as a percentage. But I cannot work out a way to specify the measure format dynamically.
I have tried formatting the Orders Completed % expression to a percentage text like this
Slicer Measure =
SWITCH(
LASTNONBLANK('Available Measures'[MeasureName], "")
, "Sales", [Sales]
, "Orders", [Orders]
, "Orders Completed %", FORMAT([Orders Completed %], "Percent")
)
but when selected the Orders Completed % does not display anything at all.
Any suggestions on how to do this?
Solved! Go to Solution.
Hi @Anonymous ,
Even in live connection you can use calculation groups and create this changes, follow the step below:
SELECTEDMEASURE()
Check result below:
I cannot send you a copy of my file since it's on direct query. But believe this can help you achieve the needed steps.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHI @MFelix , I am very interested in your solution. But I don´t know how to use it in my case. I have two measure %utilization (percentage format) and AHT (number format). I need to create calculation group. I tried.. I have a slicer with % utilization and AHT and line chart. But POWER BI gives me this error: Can´t display the data because Power BI can´t determine the relationship between two or more fields. In tabular editor I just create two calculation item (% utilization and aht) with dax measures and set format. What I doing wrong please? Thank you
Hi @Fiala ,
When you have questions about relationships probably it's because the items you are using on your visualizations are from independent tables.
You refer that you have two measures and a slicer with the % percentage utilization and AHT, is the slicer created form the calculation groups or is it from another table?
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsGreat post. Solved my requirement of dynamically changing the number format based on slicer selection (Euro / Hours)
Hi @Anonymous ,
When you use the format option on a measure or calculated column the value that you apply the format becomes a text value so this is what is happening in your chart, that is why it does not show on the line chart since when it read the values it's a text and cannot be represent in the visualization.
In this case and since you cannot show values in percentage and values by using the switch measure I believe you have one of two options:
Check both in attach PBIX file.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsTo be able to do this THE RIGHT WAY you have to use calculation groups which can be created only via Tabular Editor. It can't be done easily via the PBI interface. But if you do it thorugh the groups it'll work exactly the way you want - the measure will be dynamically formatted on the fly and it'll still be a number, not a piece of text. For how to do this, please refer to YT or www.sqlbi.com.
We are still on version SQL 2016 which does not support calculation groups. Is there a way to achieve the same functionality in SSAS 2016?
Hi @Anonymous ,
You are using direct query mode?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThe Power BI report connects to the SSAS database in "Connect Live" mode.
Hi @Anonymous ,
Following @Anonymous answer check the video below that was publish today with this exact situation.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThis video does not achieve what I have asked. The video shows measures being displayed as text values by converting the number to a text string and adding some more after the number.
What I want to achieve is to dynamically change the way Power BI formats a numeric measure depending on a a slicer value.
@Anonymous
You don't understand what the video really is all about. Alberto shows how to dynamically format a measure and the fact that he uses strings to add the unit to the number has nothing to do with this. Calculation groups let you format a measure's value DYNAMICALLY using the SELECTEDMEASUREFORMATSTRING() function. Try to watch this video once again and think about what Alberto does and how it can be used to format a number dynamically. Calculation items have properties and these properties can be set at runtime. One of the properties is the format string.
https://docs.microsoft.com/en-us/dax/selectedmeasureformatstring-function-dax
Perhaps it does, but the version of SSAS I am using is 2016, so this method is not available to me, unfortunately.
Hi @Anonymous ,
Even in live connection you can use calculation groups and create this changes, follow the step below:
SELECTEDMEASURE()
Check result below:
I cannot send you a copy of my file since it's on direct query. But believe this can help you achieve the needed steps.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix , thanks for this turorial, its really helpful. Though my issue is a little more complex. I have a target line too in my trend chart and it will change dynamically basis the measure selection from slicer. Think you can help me on that? much appreciated
Hi @haris_khan
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Is this solution possible if your data is with import mode?
Also, I do not see the external tools tab in my ribbon. Do you know what could be the reason for that?
Hi @Quiny_Harl ,
Yes this option is possible also in import mode, for the external tools to work you need to install them it's
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |