Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have been stuck at this for hours and wonder if I could get some help. Is there a way to get a value based on "unfilter" a slicer? Quick example is this.
Colour | Type | Value |
Blue | Cars | 1 |
Blue | Black | 2 |
Yellow | Black | 3 |
Yellow | Cars | 4 |
Green | Trucks | 5 |
Green | Trucks | 6 |
Let's say I have a slicer, "Colour", and "blue" is selected. I have a Matrix table with "type", and "value". Based on the slicer, only blue shows. Can I create a measure for this table that show's the value of yellow as well? What i'm trying to do is create a value, unfilter and ignore the effects of the slicer, then select colour as yellow.
I heard calculate works:
YellowOnlyValue = CALCULATE(sum(Table1[Value]),filter(Table1,Table1[Value]="Yellow"))
Any help is appreciated.
The desire output is as follows.
Type | Slicer Value | YellowOnly Value |
Cars | 1 | 3 |
Matrix | 2 | 4 |
Solved! Go to Solution.
Hi @madamadakun,
Based on my test, you should be able to use the formula below to create a new measure to calculate the Yellow Only Value in your scenario.
Yellow Only Value = VAR currentType = FIRSTNONBLANK ( Table1[Type], 1 ) RETURN CALCULATE ( SUM ( Table1[Value] ), FILTER ( ALL ( Table1 ), Table1[Colour] = "Yellow" && Table1[Type] <> currentType ) )
Regards
Hi @madamadakun,
Based on my test, you should be able to use the formula below to create a new measure to calculate the Yellow Only Value in your scenario.
Yellow Only Value = VAR currentType = FIRSTNONBLANK ( Table1[Type], 1 ) RETURN CALCULATE ( SUM ( Table1[Value] ), FILTER ( ALL ( Table1 ), Table1[Colour] = "Yellow" && Table1[Type] <> currentType ) )
Regards