topic Re: Hide subcategories which have at least one blank value in matrix column in DAX Commands and Tips

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

Sokolymbaas — Wed, 11 Sep 2024 13:06:01 GMT

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] )

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

quantumudit — Wed, 11 Sep 2024 13:54:30 GMT

Hello Sokolymbaas

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

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

Sokolymbaas — Wed, 11 Sep 2024 14:24:40 GMT

Yeah, thats excactly what i want to do.

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

Anonymous — Thu, 12 Sep 2024 07:59:59 GMT

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.