Slicer filtering a table based on more than 1 column

[Shubham04]

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 your reference. 

Table1: Campaign-Solution table where a single campaign is running for a unique solution but a single solution may have different campaigns.

Campaign   Solution

C1               S1

C2               S1

C3               S2

Note: This table is my slicer table on campaign name. 

Table2: Campaign-Account table where multiple accounts may be targeted within a single campaign

Campaign    Account

C1               A1

C1               A2

C1               A3

C2               A1

Table3: Summarized KPI table (sourced from another KPI table) with all details and relevant performance metrics for that account

Account  Solution  Contacts  Leads  Revenue

A1           S1            XXX         XXX       XXX

A1           S2            XXX         XXX       XXX

A2           S1            XXX         XXX       XXX

A3           S3            XXX         XXX       XXX

A4           S2            XXX         XXX       XXX

I already have a direct relationship between table1 & table2 on campaign name; and another between table2 & table3 on account name. 

Action: When i select C1 campaign from Table1 in the slicer, my KPI table lists as below: 

Account  Solution  Contacts  Leads  Revenue

A1           S1            XXX         XXX       XXX

A1           S2            XXX         XXX       XXX

A2           S1            XXX         XXX       XXX

A3           S3            XXX         XXX       XXX

Required Output: My KPI table should be filtered on both account and solution and listed as shown below:

Account  Solution  Contacts  Leads  Revenue

A1           S1            XXX         XXX       XXX

A2           S1            XXX         XXX       XXX

I'm stuck on this for a week and would highly appreciate any suggestion on how to make this work. Thanks in advance 🙂

-Shubham

[v-tangjie-msft · Accepted solution]

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. 

[Shubham04]

@v-tangjie-msft Geeez this is brilliant!! Exactly what i was looking for. Thanks a lot Neeko Tang 😊

[Shubham04]

Yes, campaign is connected to account, see table2 for reference.

KPI table (table3) has its own solution field.

[lukiz84]

and campaign is connected to account? which solution field is on the matrix/table? solution from "Campaign-Solution" or  from the kpitable?

[lukiz84]

That's true, no slicer will ever work on any calculated column.

But your 3 measures are Contacts, Leads, Revenue. You could just modify those or use the calculation group.

[Shubham04]

Just tried but im still getting same output (all rows from the KPI table for all solutions). Is there any other way we can filter the table out for campaign-specific solution?

[lukiz84]

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]

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

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

I think that's faster.

[Shubham04]

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

[Shubham04]

Hi BR, 

There is no slicer for S1, yet what i want is the table to be filtered dynamically on Solution (in this case S1) too based on my Campaign selection from table1. This should happen dynamically for every campaign name when selected.

-Shubham

[lukiz84]

Hi,

where do you filter to S1? How is the desired outcome defined?

BR