Forum Discussion
Ignore column based on slicer selection
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
Based on my research, this requirement cannot be done in a matrix visual. You could use table visual to achieve it.
- Create a slicer table by Enter table.
- 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 - Add this measure to table filter.
Results.
Regards,
Charlie Liao
2 Replies
Based on my research, this requirement cannot be done in a matrix visual. You could use table visual to achieve it.
- Create a slicer table by Enter table.
- 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 - Add this measure to table filter.
Results.
Regards,
Charlie Liao
Thanks for the table context for slicer solution, it worked, only MAX function was not working with string, so i used LASTNONBLANK instead.
