Forum Discussion
Helper IVChanging format when using measures in a slicer
I have a visual where I have created a slicer to switch between different measures. Some of the measures our percentages, dollars, and just decimal. I tried changing the format, but it won't change. How do I go about changing the format when using measures in a slicer so the format is reflected in the visual? Thanks.
hi cheid_4838
In the slicer tabe, add a column that indicates the format string. For example
Measure Name Format String Dollar Value $#,# Percentage 0.00% Volume #,# Now, select the measure that switches between different measures. Go to format under the measure contextual tab. Click Dynamic from the dropdown.
Enter SELECTEDVALUE('slicer'[format string]) in the formula bar.
3 Replies
- Ritaf1983
Super User
Hi cheid_4838
To dynamically change the format of your values based on the measure selected in your slicer, you can use a combination of SWITCH and a calculated DAX measure. While Power BI does not natively support dynamic format strings for measures in field parameters, you can simulate this by creating a measure for formatting your values.Example DAX Measure
DAX
Copy code
FormattedMeasure =
VAR SelectedMeasure = SELECTEDVALUE('MeasureSelector'[MeasureName])
RETURN
SWITCH(
TRUE(),
SelectedMeasure = "Percentage Measure", FORMAT([YourPercentageMeasure], "0.00%"),
SelectedMeasure = "Currency Measure", FORMAT([YourCurrencyMeasure], "$#,##0.00"),
SelectedMeasure = "Decimal Measure", FORMAT([YourDecimalMeasure], "0.00"),
BLANK() // Default value if no measure is selected
)If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
cheid_4838I created this measure and entered the FORMAT in the return statement, but I am getting an error message shown below. I even tried it with additional parenthesis and still didn't work. Is FORMAT in the wrong location within the measure?
Allocated Tractor Cost =--Calculate the Total Count for each slicer--VAR TotalCountRouteID = CALCULATE(SUM('STOPS'[COUNT]), ALL('STOPS'[RouteID]))VAR TotalCountStopID = CALCULATE(SUM('STOPS'[COUNT]), ALL('STOPS'[StopID]))VAR TotalCountStopDescription = CALCULATE(SUM('STOPS'[COUNT]), ALL('STOPS'[StopName] ))--Calculate the Line Distribution Count for each slicer--VAR LineDistCountRouteID = DIVIDE([Line Count], TotalCountRouteID)VAR LineDistCountStopID = DIVIDE([Line Count], TotalCountStopID)VAR LineDistCountStopDescription = DIVIDE([Line Count], TotalCountStopDescription)--Apply the percentage to the cost and operational numbers--VAR AllocatedCostRouteID = CALCULATE(LineDistCountRouteID * sum(Invoice[Tractor Cost]))VAR AllocatedCostStopID = CALCULATE(LineDistCountStopID * sum(Invoice[Tractor Cost]))VAR AllocatedCostStopDescription = CALCULATE(LineDistCountStopDescription * sum(Invoice[Tractor Cost]))--Create a measure to dynamically switch between the slicers--RETURNSWITCH(TRUE(),ISFILTERED('STOPS'[RouteID]), AllocatedCostRouteID,Format(AllocatedCostRouteID,"$#,##0.00"),ISFILTERED('STOPS'[StopID]), AllocatedCostStopID, Format(AllocatedCostStopID,"$#,##0.00"),ISFILTERED('STOPS'[StopName]), AllocatedCostStopDescription,Format(AllocatedCostStopDescription,"$#,##0.00"),SUM(Invoice[Tractor Cost]) // Default case if no slicer is selected)
- danextian
hi cheid_4838
In the slicer tabe, add a column that indicates the format string. For example
Measure Name Format String Dollar Value $#,# Percentage 0.00% Volume #,# Now, select the measure that switches between different measures. Go to format under the measure contextual tab. Click Dynamic from the dropdown.
Enter SELECTEDVALUE('slicer'[format string]) in the formula bar.
