How to count values that appear in two columns of same table when selected through Slicer

[sdm2211]

Hello everyone,

I have below table called "Matches"

Team 1	Team 2
Chennai Spure Kings	Mumbai Indians
Delhi Capital	Rajasthan Royal
Mumbai Indians	Delhi Capital
Rajasthan Royal	Chennai Super Kings
Delhi Capital	Mumbai Indians

I am adding a Slicer where there are season like 2008, 2009, 2010

i am adding a Slicer where there are teams like Chennai Super Kings, Mumbai Indians

Assume I have selected season 2010 in seasons slicer and selected Delhi Capital in team slicer then it should give the total count of matches played as 3, considering from Column Team 1 & Team 2.

I am not able to fetch this details. Please help me

Regards,

Sandeep Marathe

[mark_endicott · Accepted solution]

@sdm2211 - I would take the following steps

Create a disconnected "Team" table with the DAX below - to do this, go to the "Modelling" tab and select "New Table" then enter this in to the formula bar:

Team = DISTINCT(UNION( VALUES('Table'[Team 1]), VALUES( 'Table'[Team 2] )))

You must ensure there is no releationship to your 1st table so delete this if one is created. 

Then you can create a DAX measure that adds the two counts from each column together, use this DAX:

VAR selected_team =
    SELECTEDVALUE ( Team[Team 1] )
VAR count_1 =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Team 1] = selected_team )
VAR count_2 =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Team 2] = selected_team )
RETURN
    count_1 + count_2

And then add the "Team 1" column from your new "Team" table to a slicer:

If this works for you, please accept it as the solution to help others with the same challenge. 

[SantoshPothnak]

@mark_endicottGreat solution! I've tested it as well, and it works perfectly. 😊 I've also created an explanation video to help others. Thanks again!

https://youtu.be/ErBA9wGYZMo

[EricVieira]

TotalMatchesPlayed =
VAR SelectedTeam = SELECTEDVALUE('Teams'[Team])
RETURN
CALCULATE(
COUNTROWS('Matches'),
FILTER(
'Matches',
'Matches'[Team 1] = SelectedTeam || 'Matches'[Team 2] = SelectedTeam
)
)

TotalMatchesPlayed =
VAR SelectedTeam = SELECTEDVALUE('Teams'[Team])
VAR SelectedSeason = SELECTEDVALUE('Seasons'[Season])
RETURN
CALCULATE(
COUNTROWS('Matches'),
FILTER(
'Matches',
('Matches'[Team 1] = SelectedTeam || 'Matches'[Team 2] = SelectedTeam) &&
'Matches'[Season] = SelectedSeason
)
)

[mark_endicott · Accepted solution]

@sdm2211 - I would take the following steps

Create a disconnected "Team" table with the DAX below - to do this, go to the "Modelling" tab and select "New Table" then enter this in to the formula bar:

Team = DISTINCT(UNION( VALUES('Table'[Team 1]), VALUES( 'Table'[Team 2] )))

You must ensure there is no releationship to your 1st table so delete this if one is created. 

Then you can create a DAX measure that adds the two counts from each column together, use this DAX:

VAR selected_team =
    SELECTEDVALUE ( Team[Team 1] )
VAR count_1 =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Team 1] = selected_team )
VAR count_2 =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Team 2] = selected_team )
RETURN
    count_1 + count_2

And then add the "Team 1" column from your new "Team" table to a slicer:

If this works for you, please accept it as the solution to help others with the same challenge. 

[bhanu_gautam]

@sdm2211 ,Create a new measure to count the matches played by the selected team in the selected season. Assuming you have a column named Season in your table, the DAX formula would look like this:

Total Matches Played =
VAR SelectedTeam = SELECTEDVALUE('Teams'[Team])
VAR SelectedSeason = SELECTEDVALUE('Seasons'[Season])
RETURN
CALCULATE(
COUNTROWS('Matches'),
FILTER(
'Matches',
('Matches'[Team 1] = SelectedTeam || 'Matches'[Team 2] = SelectedTeam) &&
'Matches'[Season] = SelectedSeason
)
)

[sdm2211]

@bhanu_gautam @EricVieira The Measure you gave is not working. I have created measure you gave but it is giving (BLANK)

my data model is as above

1st Relation is from table matches - id to table deliveries - match_id

2nd Relation is from Table Teams-Team Names to table matches-team1

[mark_endicott]

@sdm2211 - Did you try my solution? My screenshot shows it works. 

Attached is the PBIX.

The reason the other measures will not work is because of your 2nd relationship. This is why my solution uses a disconnected "Team" table. 

[sdm2211]

@mark_endicott thanks a lot for your help, It is counting as per my expectation. I just observed when in team slicer when I select "ALL", total number of matches shows as (BLANK). But if selected single value in slicer then it is showing correctly. I am trying to attached pbix file here, but not allowing

[mark_endicott · Accepted solution]

@sdm2211 - You didn't specify this as a requirement, I've ammended my DAX measure below for to account for someone selecting "All" for the teams - here it will now just count the number of matches. 

VAR selected_team =
    SELECTEDVALUE ( Team[Team 1] )
VAR count_1 =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Team 1] = selected_team )
VAR count_2 =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Team 2] = selected_team )
RETURN
    IF ( HASONEVALUE ( Team[Team 1] ), count_1 + count_2, COUNTROWS ( 'Table' ) )

Also re-attached PBIX file. 

[sdm2211]

@mark_endicott You are a Magician 😀. That additional line made wonders 😀