Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Karthi_0502
New Member

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 : 
Dynamic_Collection = SWITCH(
    TRUE(), SELECTEDVALUE('Total/Count'[Column1]) = "$ Value",[Total],SELECTEDVALUE('Total/Count'[Column1]) = "Voucher",[Count])

Format DAX code:
VAR
typevalue = SELECTEDVALUE('Total/Count'[Column1])
VAR measureValue = SELECTEDMEASURE()  -- Store the selected measure once

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"),  -- Ensure plain whole numbers
    FORMAT(measureValue, "0")  -- Default case
)

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?



Karthi_0502_0-1739789963833.png

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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”

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

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”

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
FarhanJeelani
Super User
Super User

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.


Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.