topic Re: How to count values that appear in two columns of same table when selected through Slicer in DAX Commands and Tips

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

sdm2211 — Tue, 07 Jan 2025 11:49:17 GMT

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

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

bhanu_gautam — Tue, 07 Jan 2025 11:53:38 GMT

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
)
)

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

mark_endicott — Tue, 07 Jan 2025 12:01:46 GMT

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.

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

EricVieira — Tue, 07 Jan 2025 12:05:44 GMT

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
)
)

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

sdm2211 — Tue, 07 Jan 2025 14:05:11 GMT

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

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

mark_endicott — Tue, 07 Jan 2025 14:25:18 GMT

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.

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

sdm2211 — Tue, 07 Jan 2025 17:23:02 GMT

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

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

mark_endicott — Tue, 07 Jan 2025 17:32:18 GMT

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.

Also re-attached PBIX file.

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

sdm2211 — Tue, 07 Jan 2025 17:45:28 GMT

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

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

SantoshPothnak — Tue, 07 Jan 2025 17:56:20 GMT

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