Hide subcategories which have at least one blank value in matrix column

[Sokolymbaas]

Hello! 
Could you please help me to solve the problem?

I got matrix visual, in rows - categories and subcategories, in columns - month number in year, in values = average amount of sold products. As you see not all subcategories have sales in every month. So i would like to hide these kind of categories dynamically.

I have tried to set a filter on visual "Metric does not equal blank" but it hasn't worked.
I attached matrix example and measure i use: AVERAGE ( 'fct_table'[ValueColumn] )

[Anonymous · Accepted solution]

Hi @Sokolymbaas ,

I have created an example using this data that you can adapt to your situation.

1. Create a measure and calculate the average.

Measure = 
AVERAGE(financials[ Sales])

2. create measure and set all rows with blank values to blank.

Measure2 =
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( financials[Month Number] ),
        FILTER (
            ALL ( 'financials' ),
            'financials'[Product] = MAX ( 'financials'[Product] )
                && 'financials'[Discount Band] = MAX ( 'financials'[Discount Band] )
        )
    )
RETURN
    IF (
        ISINSCOPE ( financials[Discount Band] )
            && _count <> 12,
        BLANK (),
        [Measure]
    )

3. Be careful not to check show items with no data.

The following figure shows the different cases of using measure and measure2, more details can be found in the attachment.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[quantumudit]

Hello @Sokolymbaas 

Do you want to hide the entire sub-category row even if a single month doesn't have the sales value?

[Sokolymbaas]

Yeah, thats excactly what i want to do.