Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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.
Thank you @KRISHP1234 and @v-tangjie-msft.
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 @v-tangjie-msft works like a charm 🙂
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.
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |