Hide Measures Column (of different FORMAT) Column if contains no data

[mbudiman]

Hello,

I have requirement to hide Measure Values when it contains no data. Using 'Display Parameter' achieve the requirement. However, the Measures contains 'Currency' format and 'Whole Number' format. When Currency and Whole Number Measures are used in Display Parameter, I can only choose 1 format. How to achieve requirement to hide Measures dynamically when there is no value and yet display the format correctly (currency and whole number) ?

I have created sample data to illustrate the desired outcome here. Hide Measure if no value.pbix

[danextian · Accepted solution]

Create  these measures:

Display Measure Font Color = 
VAR _order =
    SELECTEDVALUE ( 'Display Measure'[Display Measure Order] )
RETURN
    SWITCH (
        TRUE (),
        _order IN { 2, 3 }, "blue",
        _order IN { 0, 1 }, "green",
        _order = 4
            && [Variant Qty] < 0, "red",
        _order = 7
            && [Backlog Qty] > 100, "red"
    )

Display Measure Background Color = 
VAR _order =
    SELECTEDVALUE ( 'Display Measure'[Display Measure Order] )
RETURN
    IF ( _order = 4 && [Variant Qty] < 0, "yellow" )

[danextian]

Hi @mbudiman 

Field Parameters are pseudo-dimensions that act as a measure or field selector. They aren't actual columns so in case of measures they are still strictly measures. Measures as a row or column in a matrix will remain visible even if they have blank values.

Also, instead of using FORMAT in a formula which converts the value to a text, use dynamic format strings instead.

Display Measure Value = 
SWITCH (
    SELECTEDVALUE ( 'Display Measure'[Display Measure Order], 0 ),
    0, [Forecast],
    1, [Forecast Amount],
    2, [Shipped],
    3, [Sales Revenue],
    4, [Variant Qty],
    5, [Shippable Qty],
    6, [Shippable Rev],
    7, [Backlog Qty],
    8, [Backlog Amount]
)

Please see the attached pbix.

[MasonMA]

Simpler approach than calculation group. thanks for sharing!

[mbudiman]

hi Danextian,

Thank you for providing the solution. It works great 😀 for the stated requirement.

However, now I hit another roadblock using Field Parameters. I cannot do conditional visual formating using 'Spefic Column' and 'Cell Elements' . Example :

1. to set the font color for Shipped and Sales Revenue measure column in Blue color, while Forecast and Forecast Amount column in green color.

2. If Variant Qty is less than 0, set the font color red and background yellow, e.g : -90

3. Set all Backlog Qty is red if the value is more than 100

Appreciate your further advise to this challenge.

[danextian]

Hi @mbudiman 

You will need to create a conditional formatting measure and use it as a field value in conditional formatting dialogue

VAR _order =
    SELECTEDVALUE ( 'Display Measure'[Display Measure Order], 0 )
RETURN
    SWITCH (
        _order,
        0, IF ( [Display Measure Value Variance] > 0, "named color/hex/rgb", "named color/hex/rgb" ),
        <other conditions>
    )

[mbudiman]

hi Danextian,

Thanks again for the solution. Do you create the conditional formatting measure as new measure at 'Display Measure' field parameter. Can you show 2 examples and provide in pbix :

1. Set font for Sales Revenue to blue

2. Set font for Variant Qty to red if less than zero. 

[danextian · Accepted solution]

Create  these measures:

Display Measure Font Color = 
VAR _order =
    SELECTEDVALUE ( 'Display Measure'[Display Measure Order] )
RETURN
    SWITCH (
        TRUE (),
        _order IN { 2, 3 }, "blue",
        _order IN { 0, 1 }, "green",
        _order = 4
            && [Variant Qty] < 0, "red",
        _order = 7
            && [Backlog Qty] > 100, "red"
    )

Display Measure Background Color = 
VAR _order =
    SELECTEDVALUE ( 'Display Measure'[Display Measure Order] )
RETURN
    IF ( _order = 4 && [Variant Qty] < 0, "yellow" )

[mbudiman]

hi Danextian,

I try to follow all the steps from your solution above. What is the step to create dynamic format strings ? 

[danextian]

Creating a measure is not necessary to appy dynamic format strings. Select the measure and look for these  options from the dropdown

The dynamic format string formula assumes that you there is already a format string column in your table thus is only needs to select a value from it. Please refer to the pbix files attached in my previous replies.

[mbudiman · Accepted solution]

Got it. Thanks again for the solution with detail explanation. Really appreciate it.

[mbudiman]

hi DataNinja777,

Thank you for the solution using Calculation group. It is interesting and I am keen to explore. As I am new to calculation group, can you provide me syntax for few of the Measures in my pbix, e.g Forecast Qty, Forecast ASP, Sales Revenue. 

Thank you in advance.

[MasonMA]

Hi,

You can also update your Display Measures to 

_Display Measures = 
SWITCH(
    SELECTEDVALUE('Display Measure'[Display Measure Order]),
    0, IF(ISBLANK([Forecast]), BLANK(), FORMAT([Forecast], "#,0")),                   
    1, IF(ISBLANK([Forecast Amount]), BLANK(), FORMAT([Forecast Amount], "$#,0.00")), 
    2, IF(ISBLANK([Shipped]), BLANK(), FORMAT([Shipped], "#,0")),                     
    3, IF(ISBLANK([Sales Revenue]), BLANK(), FORMAT([Sales Revenue], "$#,0.00")),     
    4, IF(ISBLANK([Shippable Qty]), BLANK(), FORMAT([Shippable Qty], "#,0")),         
    5, IF(ISBLANK([Shippable Rev]), BLANK(), FORMAT([Shippable Rev], "$#,0.00")),     
    6, IF(ISBLANK([Backlog Qty]), BLANK(), FORMAT([Backlog Qty], "#,0")),             
    7, IF(ISBLANK([Backlog Amount]), BLANK(), FORMAT([Backlog Amount], "$#,0.00"))    
)

Column subtotal can be switched off since it doesn't make sense to aggregate on row level. 

[mbudiman]

hi MasonMA,

Thank you for providing solution to format the syntax. Yes, it works 😀

[DataNinja777]

Hi @mbudiman ,

This is a classic challenge in Power BI. The issue arises because Field Parameters create a single column in your visual, which can only have one data format. The best-practice solution is to use Calculation Groups, which allow each measure to retain its unique formatting while being displayed dynamically. This method requires a free external tool like Tabular Editor, which can be launched from the "External Tools" ribbon in Power BI Desktop.

First, you'll create the calculation group in Tabular Editor by right-clicking the Tables folder and selecting Create > Calculation Group. You can rename this group to Dynamic Measures and its default column to Metric. Next, for each measure you want to display, you will create a corresponding calculation item. You do this by right-clicking the Calculation Items folder and selecting New Calculation Item. Name it appropriately (e.g., Sales Revenue) and set its expression to your existing base measure, like [Sales Revenue Measure].

The key step is to assign a specific format to each item. Select a calculation item for a currency measure, and in its Format String Expression property, enter the currency format as a text string.

"$#,##0"

For a whole number measure like Variant Qty, you would use a different format string in its properties.

"#,##0"

After setting the format for all items, save the changes in Tabular Editor. Back in Power BI, you can configure your matrix. Remove the old Field Parameter and drag your new Metric column into the Columns well of the visual, typically under your date field. Then, drag just one of your original measures into the Values well. The calculation group will automatically replace this with the correct measure and format for each column. This setup also ensures that if a measure has no data for a period, it returns BLANK, and the matrix visual will automatically hide that column.

An alternative, though not recommended, is to create a new set of measures using the FORMAT function to convert your numbers into text.

Sales Revenue (Formatted) = FORMAT( [Sales Revenue Measure], "$#,##0" )

Variant Qty (Formatted) = FORMAT( [Variant Qty Measure], "#,##0" )

You could then use these new text-based measures in your Field Parameter. However, this approach has significant downsides. It converts your numbers to text, which breaks correct numeric sorting (e.g., "$10,000" would sort before "$2,000") and prevents you from using these outputs in other visuals like charts that require a numeric data type. For these reasons, using Calculation Groups is the far superior and more robust solution.

Best regards,