Forum Discussion

Shubham04's avatar
Shubham04
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago
Solved

Slicer filtering a table based on more than 1 column

Hi everyone,    Problem statement is pretty simple that i want to dynamically filter a table based on more than 1 columns (or keys) upon my slicer selection. Here is the sample data structure for y...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Shubham04 ,

     

    According to your description, you want to use 'Table1'[Campaign] as the slicer and use the corresponding [Solution] field to filter the corresponding KPI table. Is that right?

    You can refer to the following steps:

    (1) My test data is the same as yours.

    (2) We can create a measure:

    Measure = var _slicer =VALUES('Table1'[Solution])

var _solution = MAX('Table3'[Solution])

return

IF( _solution in _slicer ,1,0)

    (3) Then we help the measure to the corresponding visual "Filter on this visual", and then we can meet your needs.

     

    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. 

lukiz84's avatar
lukiz84
Icon for Memorable Member rankMemorable Member
3 years ago

Ah ok,

 

then if it's only for one measure, you have to do it there:

 

YourMeasure =
    VAR ValidSolutions = 
           VALUES(CampaignSolution[Solution])
    RETURN
      CALCULATE(
        youMeasureCode,
        FILTER(
           KPITable,
           KPITable[Solution] IN ValidSolutions
        )

 

or if you want it for all of your measures I would suggest a calculation group with just one default item and the code:

 

Item =
    VAR ValidSolutions = 
           VALUES(CampaignSolution[Solution])
    RETURN
      CALCULATE(
        SELECTEDMEASURE(),
        FILTER(
           KPITable,
           KPITable[Solution] IN ValidSolutions
        )
  • lukiz84's avatar
    lukiz84
    Icon for Memorable Member rankMemorable Member
    3 years ago

    you could also replace the FILTER(...) part with:

     

    TREATAS(VALUES(CampaignSolution[Solution]), KPITable[Solution])

     

    I think that's faster.

    • Shubham04's avatar
      Shubham04
      Icon for Microsoft Employee rankMicrosoft Employee
      3 years ago

      Yes this works for measure, instead I'm using selectedvalue(Table1[Solution]) to get aggregated metrics, But this doesn't work in case of calculated column which im thinking could be used to filter out rows and view in the table visual as required- 

       

      Account  Solution  Contacts  Leads  Revenue

      A1           S1            XXX         XXX       XXX

      A2           S1            XXX         XXX       XXX