Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
msundelius
Frequent 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
v-alq-msft
Community Support
Community Support

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
v-alq-msft
Community Support
Community Support

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.

 

Thank you Allan.

The solution works fine.

msundelius
Frequent Visitor

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.


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

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. 

Ashish_Mathur
Super User
Super User

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/
v-alq-msft
Community Support
Community Support

Hi, @msundelius 

 

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

 

Best Regards

Allan

 

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

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.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.