DAX Measure filter value different slicer's selected value

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

@Anonymous Hi, 

Thank you again. I applied your solution and I have one question, if there is multiple values selected in the slicer, what should I adjust in the measure please ? 

Regards,

Tg

[Anonymous · Accepted solution]

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.

[Anonymous]

@Anonymous Many thanks for your help. 😊

[Anonymous]

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. 

[mh2587]

Measure = //Try this
COUNTROWS (
    FILTER (
        ALLSELECTED ( 'TableName' ),
        'TableName'[Team] <> SELECTEDVALUE ( 'TableName'[Team] )
            && 'TableName'[Status] IN {"A", "B", "C"} // Specify the certain statuses here
    )
)

[Anonymous]

@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.