Forum Discussion
DAX Measure filter value different slicer's selected value
Hi,
I have the following table :
| Name | Status | Team |
| AHP 1 | A | Team A |
| AHP 2 | B | Team A |
| AHP 3 | C | Team A |
| AHP 4 | A | Team B |
| AHP 5 | B | Team B |
| AHP 6 | C | Team B |
| AHP 7 | A | Team B |
| AHP 8 | B | Team B |
| AHP 9 | C | Team B |
| AHP 10 | A | Team B |
| AHP 11 | B | Team B |
| AHP 12 | C | Team C |
| AHP 13 | A | Team C |
| AHP 14 | A | Team C |
| AHP 15 | A | Team C |
| AHP 16 | B | Team C |
| AHP 17 | C | Team D |
| AHP 18 | A | Team D |
| AHP 19 | B | Team D |
| AHP 20 | C | Team D |
I would like to count Name of certain Status which have the Team different from the Team selected in slicer.
For example : when I select "Team B" in slicer
The "Team B" should be excluded from the measure
| Name | Status | Team |
| AHP 1 | A | Team A |
| AHP 13 | A | Team C |
| AHP 14 | A | Team C |
| AHP 15 | A | Team C |
| AHP 18 | A | Team D |
| AHP 2 | B | Team A |
| AHP 16 | B | Team C |
| AHP 19 | B | Team D |
| AHP 3 | C | Team A |
| AHP 12 | C | Team C |
| AHP 17 | C | Team D |
| AHP 20 | C | Team D |
So the result of measure should be :
| Status | Name |
| A | 5 |
| B | 3 |
| C | 4 |
thanks in advance
Regards,
Tg
Hi Anonymous ,
Based on my testing, if there is multiple values selected in the slicer, please try the following methods:
1.Drag the Table Team field into the slicer visual.
2.Create the new measure to calculate the number for each status.
Total team = CALCULATE( COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[Status]) )3.Create the new measure to calculate the selected values on a slicer.
Team selected = var tea = VALUES('Table'[Team]) RETURN CALCULATE( COUNTROWS('Table'), FILTER(ALLEXCEPT('Table', 'Table'[Status]), 'Table'[Team] in tea) )4.Create the new measure to calculate the values exclude the slicer selected.
Exclude selected = [Total team] - [Team selected]5.Drag the measure into the table visual. The result is shown below.
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Measure = //Try this COUNTROWS ( FILTER ( ALLSELECTED ( 'TableName' ), 'TableName'[Team] <> SELECTEDVALUE ( 'TableName'[Team] ) && 'TableName'[Status] IN {"A", "B", "C"} // Specify the certain statuses here ) )mh2587 Thank you for your prompt response.
The measure doesn't sort out the expected Name.
The expected result is not a table (I wasn't clear enough, sorry).
I would like to create 3 cards (for 3 statuses) which count the Name having the different group from the slice's selected value , then I can export these names.
Hi Anonymous ,
First of all, many thanks to mh2587 for your very quick and effective replies.
Based on my testing, please try the following methods:
1.Create the simple table.
2.Drag the slicer table Team field into the slicer visual.
3.Create the new measure to filter value.
Measure_Name = VAR SelectedTeam = SELECTEDVALUE('Table slicer'[Team]) RETURN CALCULATE( COUNTROWS('Table'), FILTER( ALLEXCEPT('Table', 'Table'[Status]), 'Table'[Team] <> SelectedTeam ) )4.Select Drag the status field and measure into the table visual.
5.The result is shown below.
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your time and quick reply. The table has more than 20K lines, I would like to find a solution without create new table.
In addition, the expect result is not a table (I wasn't clear enough, sorry).
I would like to create 3 cards (for 3 statuses) which count the Name having the different group from the slice's selected value , then I can export these names.
Hi Anonymous ,
Based on my testing, if there is multiple values selected in the slicer, please try the following methods:
1.Drag the Table Team field into the slicer visual.
2.Create the new measure to calculate the number for each status.
Total team = CALCULATE( COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[Status]) )3.Create the new measure to calculate the selected values on a slicer.
Team selected = var tea = VALUES('Table'[Team]) RETURN CALCULATE( COUNTROWS('Table'), FILTER(ALLEXCEPT('Table', 'Table'[Status]), 'Table'[Team] in tea) )4.Create the new measure to calculate the values exclude the slicer selected.
Exclude selected = [Total team] - [Team selected]5.Drag the measure into the table visual. The result is shown below.
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
