The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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?
Solved! Go to Solution.
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”
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”
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")
)
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.
User | Count |
---|---|
10 | |
9 | |
6 | |
6 | |
5 |
User | Count |
---|---|
22 | |
14 | |
14 | |
9 | |
7 |