Hide/Show column dynamically depending if the total equals zero

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.

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 BI | Medium

 

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.