Hide/Show column dynamically depending if the total equals zero

[brdrok]

Hi,

I have a table inside Power BI where two columns total to $0.00.  I would like to hide those two columns if/when the totals equals to $0.00

Is that possible to do so in Power BI for the table visualization?  

Thank you,

[danextian · Accepted solution]

Hi @brdrok 

You cannot hide a measure that's been added to a viz even if that measure returns a blank but you can with a column category. You will need a disconnected table that has a column, containing the measure names, which you can create with DAX or M/Enter data like below:

Then write a measure that switches to different measures depending on the category value

MeasureSwitch =
SWITCH (
    SELECTEDVALUE ( _values[Measures] ),
    "Revenue", [Total Revenue],
    "Transactions", [Total Transactions],
    "Dummy",
        VAR __Dummy =
            -- apply the same value to all category rows
            CALCULATE (
                [Dummy Measure],
                ALLSELECTED ( Category[Category] )
            )
        RETURN
            IF ( __Dummy <> 0, __Dummy )
)

ALL/ALLSELECTED must be applied to all the fields added to the row tile.

Notice that the first matrix has a column and a single measure only while the second one doesnt have a column but has three measures.

Please see attached sample pbix.

[brdrok]

Hi @Anonymous  and @danextian .  Sorry for the late response.  Between being a newbie at Dax/Power BI and spending a "wonderful" afternoon at the dentist, it took me a little while to get the idea for the wonderful solutions.  Tried both and I kinda got it to work.  Still need to lear more but I got the idea.  Is there a way to accept more than one solution?

[danextian]

You should be able to accept multiple solutions.

[Anonymous · Accepted solution]

Thanks for the reply from Irwan, please allow me to provide another insight.
Hi @brdrok ,

It is unlikely to be possible to implement autohide columns in the table visualization.
This usually requires manual control of column hiding with the help of field parameters.

Show/Hide Columns Dynamically Using Field Parameters — Power BI | by Monika Mishra | Microsoft Power...

However, this may be possible to realize in a matrix.

The test data table is as follows.

Create a calculation table that uses a column to contain the names of the columns that need to be dynamically hidden.

Create two measures.

Result = 
SWITCH(
    SELECTEDVALUE(ColumnName[columns]),
    "Test1",SUM('Table'[Test1]),
    "Test2",SUM('Table'[Test2]),
    "Test3",SUM('Table'[Test3])
    )

Value = 
IF(CALCULATE([Result],ALL('Table'[Date]))=0,BLANK(),[Result])

Create a matrix.
You can see that the format of the matrix is almost the same as the table visualization and that the columns with a Total of 0 are automatically hidden.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian · Accepted solution]

Hi @brdrok 

You cannot hide a measure that's been added to a viz even if that measure returns a blank but you can with a column category. You will need a disconnected table that has a column, containing the measure names, which you can create with DAX or M/Enter data like below:

Then write a measure that switches to different measures depending on the category value

MeasureSwitch =
SWITCH (
    SELECTEDVALUE ( _values[Measures] ),
    "Revenue", [Total Revenue],
    "Transactions", [Total Transactions],
    "Dummy",
        VAR __Dummy =
            -- apply the same value to all category rows
            CALCULATE (
                [Dummy Measure],
                ALLSELECTED ( Category[Category] )
            )
        RETURN
            IF ( __Dummy <> 0, __Dummy )
)

ALL/ALLSELECTED must be applied to all the fields added to the row tile.

Notice that the first matrix has a column and a single measure only while the second one doesnt have a column but has three measures.

Please see attached sample pbix.

[brdrok]

Hi @Irwan ,

thank you for your response.  If I am reading it right, it's suppressing the row that is 0, right?  If so, what I would like to accomplish instead is to hide a column if the total equals to $0.00.

[Irwan]

hello @brdrok 

i am not sure but hiding a column is not likely to be done since you add those column to your visual.

Thank you.

[Irwan]

hello @brdrok 

is it plausible to do it with visual filter in your case?
here is a simple example:
- before filter

- after filter

Hope this will help.

Thank you.