Forum Discussion
Microsoft EmployeeSlicer 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 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
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.
11 Replies
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.
Shubham04Anonymous Geeez this is brilliant!! Exactly what i was looking for. Thanks a lot Neeko Tang 😊
lukiz84Memorable Member
Hi,
where do you filter to S1? How is the desired outcome defined?
BR
- 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
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
- 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
and campaign is connected to account? which solution field is on the matrix/table? solution from "Campaign-Solution" or from the kpitable?
- Shubham04
Yes, campaign is connected to account, see table2 for reference.
KPI table (table3) has its own solution field.