Analyse & slice data by delimiter, but not split rows.

[BassG]

Hi all,

I've got an issue that I can't seem to work out & looking for help on what the best solution would be.

My data looks a little like this...

Studen t ID	SIMD	Sport 1	Sport 2	Sport 3	Sport 4	Sport 5	Concat Sport	Has Sport	Count
1234	2	Football	Rugby				Football,Rugby	1	1
2341	2	Football	Badminton	Judo			Football,Badminton,Judo	1	1
3412	4	Rugby	Football				Rugby,Football	1	1
4123	3	Shinty	Rounders	Football	Judo		Shinty,Rounders,Football,Judo	1	1
4321	3	Football					Football	1	1
2413	1	Rugby	Shinty	Rounders	Judo	Badminton	Rugby,shinty,Rounders,Judo,Badminton	1	1
1324	5	Badminton	Judo				Badminton,Judo	1	1
1221	5							0	1
1323	3							0	1
2211	2							0	1

What I'd like to be able to do is have a couple of graphs that show % of students in each SIMD that have taken up a sport & the % of sport students in each SIMD - like these>

   

For the first one, I have a simple measure that says "Sport Percentage = (SUM('2024-25 T1'[Has Sport]))/(SUM('2024-25 T1'[Count]))".  The 2nd one is just "Show value as %age of grand total".

I want to have a single slicer that allows me to filter by sport.  I can split rows by delimiter on the "Concat Sport" column, but then I get multiple counts of each student under the "Has Sport" column.

Anyone know a way around this?

EDIT:
I've created a merged column using Student ID & Has Sport so the new "ID Sport" column has "1234 1", "2341 1", "1221 0", etc.  So what I need to work out is how to create a measure that does a distinct count version of this - "Sport Percentage = (SUM('2024-25 T1'[Has Sport]))/(SUM('2024-25 T1'[Count]))".  But instead of "Has Sport" & "Count", it's "ID Sport" and "Student ID".  Does that make sense?

[v-echaithra]

Hi @BassG ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra E.

[v-echaithra]

Hi @BassG,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Chaithra E.

[v-echaithra]

Hi @BassG,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra E.

[SamWiseOwl]

Hi @BassG 

Instead of splitting Concat Sport into columns consider splitting it into rows.

I've attached a file that splits it into rows and seperates sports into another table.

[BassG]

I think you're on the right track with having a table of the sports, but you maybe misunderstood what I'm trying to do?

I want to have graphs like the 2 above - showing the % in each SIMD band (1-5) but with a single dropdown slicer for the sport.
If I use split by row and no individual sport is selected, it counts "Has Sport" multiple times for each student with more than 1 sport.

I wonder if I use a concat with the Student ID for "Has Sport", if that would work with the Split Rows by Delimiter?

[SamWiseOwl]

Hi @BassG 
I am not fully understanding the goal.

Using SIMD 3 what is your assumption?

Mine would be:
All Sports selected: 2 / 3 = 66%

Judo: 1/3 = 33%

Football: 2/3 = 66%

4123	3	Shinty	Rounders	Football	Judo		Shinty,Rounders,Football,Judo	1	1
4321	3	Football					Football	1	1
1323	3							0	1

[SamWiseOwl · Accepted solution]