Forum Discussion

Kransky77's avatar
Kransky77
New Member
2 months ago
Solved

Dynamic formatting shows only some data correctl in matrix table.

Hello, I am currently expiriencing very strange problen with matrix table - only some numbers showing correctly wherea the rest is still not keeping the formats. My format string looks like var _...
Need Help
  • johnt75's avatar
    johnt75
    2 months ago

    It looks like the values which aren't being correctly formatted exceed 1,000,000,000 which is the largest value you're testing for. Try using the format value for millions as the default, instead of putting a cap on it

    Format string =
VAR turnover = [turnover]
VAR _currentvalue =
    SWITCH ( TRUE (), 
	turnover < 1000, "0", 
	turnover < 1000000, "#,.0K", 
	"#,,.0M" 
)
RETURN
    _currentvalue
  • Parchitect's avatar
    Parchitect
    2 months ago

    Hi Kransky77,

    This does not look like a semantic model issue to me. It looks like the dynamic format string is working only for the ranges you defined.

    In your current logic, you handle values below 1 billion:

    < 1,000          → "0"
< 1,000,000      → "#,.0K"
< 1,000,000,000  → "#,,.0M"

    But in your screenshot, some values are greater than 1 billion, for example:

    1817016138
14604670286
7526320454

    For those rows, none of your SWITCH conditions are true, so the dynamic format string has no matching result and Power BI falls back to the unformatted number.

    Try adding a billion format and a final fallback:

    VAR _value =
    ABS ( [turnover] )
RETURN
SWITCH (
    TRUE(),
    _value < 1000, "0",
    _value < 1000000, "#,.0K",
    _value < 1000000000, "#,,.0M",
    _value < 1000000000000, "#,,,.0B",
    "#,0"
)

    Using ABS() also helps if the turnover measure can return negative values.

    If this still does not work, I would then check whether another calculation group or another measure format string is overriding the format. But based on the screenshot, the most likely issue is simply the missing condition for values above 1 billion.

  • tanisha_bh09's avatar
    tanisha_bh09
    2 months ago

    Hi,

    This may be happening because your dynamic format string doesn't have a default format for values greater than or equal to 1 billion.
    SWITCH(
    TRUE(),
    [turnover] < 1000, "0",
    [turnover] < 1000000, "#,.0K",
    [turnover] < 1000000000, "#,,.0M",
    "#,,,.0B"
    )

    Also, dynamic format strings can sometimes behave differently in Matrix visuals, especially with totals/subtotals. As a test, try placing the same measure in a Table visual and see if the formatting is consistent there.

    Microsoft documentation:

    Create dynamic format strings for measures - Power BI | Microsoft Learn

    If the issue persists, please share whether you're using Import or DirectQuery mode and your Power BI Desktop version.

     

     
Parchitect's avatar
Parchitect
Solution Sage
2 months ago

Hi Kransky77,

This does not look like a semantic model issue to me. It looks like the dynamic format string is working only for the ranges you defined.

In your current logic, you handle values below 1 billion:

< 1,000          → "0"
< 1,000,000      → "#,.0K"
< 1,000,000,000  → "#,,.0M"

But in your screenshot, some values are greater than 1 billion, for example:

1817016138
14604670286
7526320454

For those rows, none of your SWITCH conditions are true, so the dynamic format string has no matching result and Power BI falls back to the unformatted number.

Try adding a billion format and a final fallback:

VAR _value =
    ABS ( [turnover] )
RETURN
SWITCH (
    TRUE(),
    _value < 1000, "0",
    _value < 1000000, "#,.0K",
    _value < 1000000000, "#,,.0M",
    _value < 1000000000000, "#,,,.0B",
    "#,0"
)

Using ABS() also helps if the turnover measure can return negative values.

If this still does not work, I would then check whether another calculation group or another measure format string is overriding the format. But based on the screenshot, the most likely issue is simply the missing condition for values above 1 billion.