topic Re: Hide matrix rows where all columns have value 0 in DAX Commands and Tips

Hide matrix rows where all columns have value 0

vsp_p1 — Wed, 24 Apr 2024 09:44:56 GMT

Dear experts,

Link to pbix: https://drive.google.com/file/d/1g36HsepbL5tGJxrTESIxF4ueV4dlXQuo/view?usp=sharing

I'm trying to build a report to show the movement of items for the past 4 weeks (Movement = [Total Available Curr. Week] - [Total Available Last Week]) in a matrix visual. In some of the cases, there are no movements in ALL THE FOUR WEEKS, so the values are 0 FOR ALL THOSE WEEKS. I don't want those rows to appear in the matrix.

I tried filtering [Movement] <> 0 in the filter pane, but that removes the rows where at least one value is 0.

The pbix is available at the link above. Can you experts help me find a solution to my problem?

PS: I have added notes in the pbix to make it easier.

Regards, Vidyadutt

Re: Hide matrix rows where all columns have value 0

KRISHP1234 — Wed, 24 Apr 2024 10:29:04 GMT

Use This Measure
Movement =

VAR a = [Available]

VAR b =[Available LW]

VAR c = [Available] - [Available LW]

RETURN

SWITCH(TRUE(),

a = 0 && b = 0,BLANK(),

)

Re: Hide matrix rows where all columns have value 0

Anonymous — Fri, 26 Apr 2024 09:13:48 GMT

Hi vsp_p1 ,

Thank you KRISHP1234 for your reply and solution. He is valid when both [Available] and [Available LW] are 0.
If your [Available] is some other value but the difference is 0, you can refer to the following solution:

(1)Create measures.

Measure = var a=ADDCOLUMNS(CROSSJOIN(ALLSELECTED('Product'[Product Name]),ALLSELECTED('Week'[Week Name])),"move",[Movement]) var b=SUMX(FILTER(a,[Product Name] in VALUES('Product'[Product Name])&&[Week Name] in VALUES(Week[Week Name])&&[move]<>0),[move]) return b

Flag = IF([Measure]=BLANK(),0,1)

(2)Place [Flag=1] on the visual object filter.

Best Regards,

Neeko Tang

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

Re: Hide matrix rows where all columns have value 0

vsp_p1 — Fri, 26 Apr 2024 12:32:36 GMT

Thank you KRISHP1234 and Anonymous.

The solution by KRISHP1234 worked in the sample dataset, but it didn't work in my actual dataset as there were many more combinations.

The one from Anonymous works like a charm 🙂

Re: Hide matrix rows where all columns have value 0

Krzysztof1 — Fri, 28 Jun 2024 05:18:02 GMT

Hi guys, I have similar problem with hiding rows in matrix but it depends on values i.e I want only show categories when values of this individual category > 200 000 and without affectig existing sub total in values . Is it possible in matrix?

Re: Hide matrix rows where all columns have value 0

vsp_p1 — Fri, 28 Jun 2024 07:44:42 GMT

I think this should work.

IF(
HASONEFILTER('Category'[Category]),
IF(
SUM('Fact Table'[Value]) > 200000,
SUM('Fact Table'[Value])
BLANK()
),
SUM('Fact Table'[Value])
)

Re: Hide matrix rows where all columns have value 0

Krzysztof1 — Fri, 28 Jun 2024 09:19:35 GMT

vsp_p1 Many Many thanks !

You help me a lot. It works!

Greetings from PL Have a nice weekend!

Re: Hide matrix rows where all columns have value 0

vsp_p1 — Fri, 28 Jun 2024 09:50:26 GMT

Anytime, mate!!