Ignore column based on slicer selection

[irfanarif1]

I have two values in Performance colum 'Actual and Variance' and Reason for Variance itself.

what i am trying to do is creating a report having color in rows, performance in column, amount in values. While i created a slicer for reason.

Color	Performance	Reason	Amount
Green	Actual		10
Green	Variance	Known	4
Green	Variance	Unknown	3

This looks like below report. but when I use the slicer for reason to select known variances it hides the Actual colum from pivot.

Sum of Amount	Performance
Color	Actual	Variance
Green	10	7

Hope i explained it well, Kindly let me know if i can use filter for varaince and ignore the selection for actual.

Sum of Amount	Performance
Color	Variance
Green	4

dont want to hide Actual column over selection

[v-caliao-msft · Accepted solution]

@irfanarif1,

Based on my research, this requirement cannot be done in a matrix visual. You could use table visual to achieve it.

1. Create a slicer table by Enter table.
2. Create a measure in Original table.
   Measure =
   var selecteditem = IF(HASONEVALUE(Slicer[Slicer]),LASTNONBLANK(Slicer[Slicer],1),BLANK())
   var check = IF((MAX(Table1[Performance])="Actual")||ISBLANK(selecteditem),1,IF(selecteditem=MAX(Table1[Reason]),1,0))
   return check
3. Add this measure to table filter.
   

Results.

Regards,

Charlie Liao

[irfanarif1]

Thanks for the table context for slicer solution, it worked, only MAX function was not working with string, so i used LASTNONBLANK instead.