Forum Discussion

irfanarif1's avatar
irfanarif1
New Member
8 years ago
Solved

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...
  • v-caliao-msft's avatar
    v-caliao-msft
    8 years ago

    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

     

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
8 years ago

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's avatar
irfanarif1
New Member
8 years ago

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