cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Player attendance by training group

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

msundelius_0-1593789562076.png

 

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. 

 

msundelius_1-1593789635246.png

 

I have tried this solutions: 

Created two dim_player tables. One with an active and another with an unactive relationship.

 
Count of trainings=
VAR a =
    CALCULATE (
        CALCULATE ( COUNT ( 'Fact_Training'[File] ), 'Fact_Training'[status] = "Attend" ),
        USERELATIONSHIP ( 'Fact_Training'[Name], 'Dim_Player_unactive'[Name] )
    )
RETURN
    a

 

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. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Player attendance by training group

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:

h1.png

 

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:

i1.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

8 REPLIES 8
Highlighted
Super User IV
Super User IV

Re: Player attendance by training group

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: Player attendance by training group

Hi, @msundelius 

 

I am not very clear about the expected result. Could you please explain to us about the result?

 

Best Regards

Allan

 

Highlighted
Super User IV
Super User IV

Re: Player attendance by training group

Hi,

Please expain how you arrived at the results in the matrix.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Regular Visitor

Re: Player attendance by training group

Thank you all for your comments. I have added more information to the question. Please inform me if the new information is not enough. 

Highlighted
Super User IV
Super User IV

Re: Player attendance by training group

Hi,

Show the exact result that you are expecting.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Regular Visitor

Re: Player attendance by training group

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. 

Highlighted
Community Support
Community Support

Re: Player attendance by training group

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:

h1.png

 

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:

i1.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Highlighted
Regular Visitor

Re: Player attendance by training group

Thank you Allan.

The solution works fine.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors