Forum Discussion
Issue in Dynamic Formatting
Hi,
I'm facing an issue with dynamic formatting in Power BI. I need a measure that returns either Total / Count based on the slicer selection.
- If "Total" is selected, the measure should format the value automatically in thousands (K) or millions (M) and prefix it with "$".
- If "Count" is selected, the measure should display a whole number without formatting.
DAX :
Format DAX code:
VAR typevalue = SELECTEDVALUE('Total/Count'[Column1])
The above DAX works fine for some months. However, when I change the month slicer, it sometimes returns incorrectly formatted values. For example, when selecting "Count", a value like 1740 is incorrectly displayed as 1742K instead of a whole number.
I've attached an image for reference. Can anyone help resolve this issue?
Hi,
I think, the format string for Thousand with K or Million with M should be something like below.
In your code, I think you missed some commas.
“$#,##0,.0 K”
“$#,##0,,.0M”
3 Replies
Hi Karthi_0502 ,
The issue you're experiencing stems from the use of SELECTEDMEASURE( ) within a FORMAT( ) function, combined with the SWITCH( ) logic. Power BI can apply formatting inconsistently when the measure's context changes, especially across months or with slicers.
Create two separate measures—one for display and one for calculations—to handle formatting dynamically but more reliably.
Step 1: Base Calculation Measure
Dynamic_Collection = SWITCH( TRUE(), SELECTEDVALUE('Total/Count'[Column1]) = "$ Value", [Total], SELECTEDVALUE('Total/Count'[Column1]) = "Voucher", [Count], BLANK() )Step 2: Corrected Dynamic Formatting
Formatted_Value = VAR typevalue = SELECTEDVALUE('Total/Count'[Column1]) VAR measureValue = [Dynamic_Collection] -- Use the calculated measure directly RETURN SWITCH( TRUE(), typevalue = "Total" && measureValue < 1000, FORMAT(measureValue, "$#,##0"), typevalue = "Total" && measureValue >= 1000 && measureValue < 1000000, FORMAT(measureValue / 1000, "$#,##0K"), typevalue = "Total" && measureValue >= 1000000, FORMAT(measureValue / 1000000, "$#,##0M"), typevalue = "Count", FORMAT(measureValue, "0"), FORMAT(measureValue, "0") )- Refresh visuals after changes.
- Ensure the slicer correctly filters data by testing each selection independently.
- Use table visuals to validate numeric values before applying pie charts or other visuals.
Please mark this post as solution if it helps you. Appreciate Kudos.
Thanks for replying FarhanJeelani
After I create the Formatted_Value measure and applies in the pie chart or any other visuals. It won't accepting because it was in the text datatype. I can't able to change text to anyother datatype.
Hi,
I think, the format string for Thousand with K or Million with M should be something like below.
In your code, I think you missed some commas.
“$#,##0,.0 K”
“$#,##0,,.0M”
