Matrix table: hide a column when at least one row of value is blank

[k943]

I would like to dynamically hide column when there is at least one row value is blank. The value is from a measure that calculate the average price of a product in monthly period.

In the example below, in March, certain products do not have price yet, so the averages are blank, I would like to set up a way to dynamically hide the March column (3.2024), so the matrix table will only have 1.2024 and 2.2024 columns (as in the second figure) . 

desired output:

[Anonymous]

Hi @k943 ,

I created the following formula which seems to work for your needs.

result = 
VAR _count =
    COUNTX (
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = MAX ( 'Table'[Date] ) ),
        [Date]
    )
VAR _countblank =
    COUNTX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = MAX ( 'Table'[Date] )
                && 'Table'[Value] <> BLANK ()
        ),
        [Date]
    )
RETURN
    IF ( _count <> _countblank, BLANK (), MAX ( 'Table'[Value] ) )

Best Regards,
Adamk Kong

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

[k943]

Thank you for the assistance. Unfortunately it is not working. I think it is because for rows who are blank in certain period, the values (price data) are not even in the table yet, they are not existing and therefore they are blank. That means in your DAX, 

'Table'[Value] <> BLANK ()

might need to be adjusted?  This is just my 2 cents. Let me know if you are able to resolve. Thank you very much anyway!

[lbendlin]

I would like to dynamically hide column

Let me stop you right there. Not possible in Power BI.

If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com/?forum=2d80fd4a-16cb-4189-896b-e0dac5e08b41