The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hi,
I'm coach for a soccer team and using Power BI to track training attendance.
With that in mind I wanted to see how many times different players had attended the same group with the same coach.
I have tried to solve this, but can't seem to find a solution.
Example of the data (Fact_Training):
Preferred solution (This is manual created):
Shows the number of attended training session, where the player have attended the same group.
In the example Player1 have attended 3 training sessions and have been in the same group 1 time with Player2 , Player3 , Player4 and Player5.
I have tried this solutions:
Created two dim_player tables. One with an active and another with an unactive relationship.
This solution only give me the figures for the attended training sessions , but not the part where I can see how many times the different players have trained together.
A prehand thanks to you all.
Solved! Go to Solution.
Hi, @msundelius
I am sorry for the late reply. Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Name(a calculated table):
Name = DISTINCT('Table'[Name])
There is no relationship between two tables. You may create a measure as below.
Result =
var _name1 = SELECTEDVALUE('Table'[Name])
var _name2 = SELECTEDVALUE('Name'[Name])
var tab =
ADDCOLUMNS(
SUMMARIZE(
'Table',
'Table'[Date]
),
"status1",
MAXX(
FILTER(
ALL('Table'),
'Table'[Date]=EARLIER('Table'[Date])&&
'Table'[Name]=_name1
),
[status]
),
"Group1",
CALCULATE(
MAX('Table'[Group]),
FILTER(
ALL('Table'),
'Table'[Date]=EARLIER('Table'[Date])&&
'Table'[Name]=_name1
)
),
"status2",
MAXX(
FILTER(
ALL('Table'),
'Table'[Date]=EARLIER('Table'[Date])&&
'Table'[Name]=_name2
),
[status]
),
"Group2",
CALCULATE(
MAX('Table'[Group]),
FILTER(
ALL('Table'),
'Table'[Date]=EARLIER('Table'[Date])&&
'Table'[Name]=_name2
)
)
)
var newtab =
ADDCOLUMNS(
tab,
"flag",
IF(
[Group1]=[Group2]&&[status1]="Attend"&&[status2]="Attend",
1,0
)
)
var result =
SUMX(
newtab,
[flag]
)
return
IF(
result=0,
BLANK(),
result
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @msundelius
I am sorry for the late reply. Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Name(a calculated table):
Name = DISTINCT('Table'[Name])
There is no relationship between two tables. You may create a measure as below.
Result =
var _name1 = SELECTEDVALUE('Table'[Name])
var _name2 = SELECTEDVALUE('Name'[Name])
var tab =
ADDCOLUMNS(
SUMMARIZE(
'Table',
'Table'[Date]
),
"status1",
MAXX(
FILTER(
ALL('Table'),
'Table'[Date]=EARLIER('Table'[Date])&&
'Table'[Name]=_name1
),
[status]
),
"Group1",
CALCULATE(
MAX('Table'[Group]),
FILTER(
ALL('Table'),
'Table'[Date]=EARLIER('Table'[Date])&&
'Table'[Name]=_name1
)
),
"status2",
MAXX(
FILTER(
ALL('Table'),
'Table'[Date]=EARLIER('Table'[Date])&&
'Table'[Name]=_name2
),
[status]
),
"Group2",
CALCULATE(
MAX('Table'[Group]),
FILTER(
ALL('Table'),
'Table'[Date]=EARLIER('Table'[Date])&&
'Table'[Name]=_name2
)
)
)
var newtab =
ADDCOLUMNS(
tab,
"flag",
IF(
[Group1]=[Group2]&&[status1]="Attend"&&[status2]="Attend",
1,0
)
)
var result =
SUMX(
newtab,
[flag]
)
return
IF(
result=0,
BLANK(),
result
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you Allan.
The solution works fine.
Thank you all for your comments. I have added more information to the question. Please inform me if the new information is not enough.
Hi,
Show the exact result that you are expecting.
I expect an output shown in the matrix created as a manual result in the question info.
I get the cross between Player1 and Player1 , Player2 and Player2 etc.
I want also to get the cross between Player1 and Player2 , Player1 and Player3 etc.
Hi,
Please expain how you arrived at the results in the matrix.
Hi, @msundelius
I am not very clear about the expected result. Could you please explain to us about the result?
Best Regards
Allan
@msundelius , I think one of the sides/pivot should be the group .
You can take count of attended
Measure = Calculate(count(Table[Status]),Table[Status]="Attend")
Use in Matrix with the player , Group as Row and column
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
157 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
153 | |
137 | |
131 | |
81 | |
61 |