I have a table and I am trying to compare the cost of different projects. The projects are grouped based on cluster and country in the facts table. I am trying to create two slicers based on project column. One slicer would contain all the projects irrespective of cluster and country so when a project is selected in the first slicer, the second slicer returns projects that are in the same cluster and location as the first slicer.
What I tried - I created two disconnected tables for the project slicers using the code below:
slicer1 = SUMMARIZECOLUMNS(Facts_table[project],Facts_table[country],Facts_table[cluster])
slicer2 = SUMMARIZECOLUMNS(Facts_table[project],Facts_table[country],Facts_table[cluster])
Also created two measures for the slicers which are used in the filter pane and set to 'is not blank'
Slicer1 count = CALCULATE(COUNTROWS('Facts_table'),'Facts_table'[project] = SELECTEDVALUE('Slicer1'[project]))
Slicer2 count = CALCULATE(COUNTROWS('Facts_table'),'Facts_table'[project] = SELECTEDVALUE('Slicer2'[project]))
With this, I am able to get the costs of the different projects in the table.
What I am expecting - If a selection is made in slicer 1, slicer 2 returns projects in the same cluster and country as slicer 1.
Facts table
| project | dept | cluster | country | cost |
|---|---|---|---|---|
| Project A | HR | 1 | France | 10830 |
| Project b | Logistics | 1 | Poland | 11841 |
| Project c | Product design | 2 | France | 11209 |
| Project d | Transport | 1 | Turkey | 6814 |
| Project e | HR | 1 | UK | 9881 |
| Project f | Logistics | 2 | Poland | 9804 |
| Project g | Product design | 2 | Turkey | 10460 |
| Project g | Transport | 2 | France | 5486 |
| Project h | HR | 1 | Poland | 6960 |
| Project j | Logistics | 2 | France | 11098 |
| Project i | Product design | 3 | Turkey | 12276 |
| Project k | Transport | 3 | UK | 7614 |
| Project f | HR | 1 | Poland | 6039 |
| Project n | Logistics | 2 | Turkey | 6285 |
| Project m | Product design | 1 | Turkey | 8732 |
Try this for your slicer2 filter measure:
slicer 2 filter =
CALCULATE(
COUNTROWS('slicer2'),
'slicer2'[country] in DISTINCT('slicer1'[country]) &&
'slicer2'[cluster] in DISTINCT('slicer1'[cluster]) &&
NOT SELECTEDVALUE('slicer2'[project]) in DISTINCT('slicer1'[project])
)
The above will show slicer 2 as empty until a selection is made in slicer 1 (with a match).