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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Maestro
Frequent Visitor

Longitudinal injuries - how many players are injured at any timepoint

Hi all, at the moment I am working on injury data over multiple seasons.

 

Key information for everyone is: how many players do you have available. Therefore, we want to see how many players are injured at any moment in time (is it generally 3 or 8 players that you can't use). Therefore I want a graph as shown below (made in Excel): over the timeframe I want the # of players that are injured. For this purpose I have the date of occurence and date of return-to-play. In the graph you can see on the x-axis the date and on the y-axis the amount of injuries for each team (team is different colors). For example: a player in the 'blue' team is injured from 1st of June till 3rd of August --> he should be counted as 'one injury' for the whole time-period, not just the start of his injury. In addition to this player more players in this team become injured and add up.

 

Injuries_over_time.jpgI have already tried distinctcount, count or datesbetween combinations of DAX formulas, but I am too much of a beginner to get it working. I also tried to generate a new table as 'count', with date filters and summarizing countrows, but everything I try: it is not valid, unfortunately. 

Table = 
VAR ExpandedTable =     
    GENERATE(
        CALENDAR(DATE(2010,1,1),DATE(2025,12,31)),
        FILTER(
            'Blessures',
            [Date]>='injuries'[start_injury] &&
            [Date]< IF(ISBLANK('injuries'stop_injury),TODAY(),'injuries'stop_injury)
            )
            )
            
RETURN 
    SUMMARIZE(  
        ExpandedTable,
        [Date] ,
        [Team], 
        "Count",COUNTROWS('injuries')
        )   

 

I hope I showcased the problem clearly. If anything is still unclear, please let me know! Thanks in advance! 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

@Maestro 
Here is a sample file with the solution https://www.dropbox.com/t/tgnDTNTOWcYDK82x

Count Injuries = 
SUMX (
    'Date',
        SUMX (
            Blad1,
            VAR CurrentDate = 'Date'[Date]
            RETURN
                IF ( 
                    Blad1[Injury_start] <= CurrentDate
                        && OR ( ISBLANK ( Blad1[Injury_end] ), Blad1[Injury_end] > CurrentDate ),
                    1,
                    0
                )
        )
)

1.png2.png

View solution in original post

11 REPLIES 11
tamerj1
Super User
Super User

@Maestro 
Here is a sample file with the solution https://www.dropbox.com/t/tgnDTNTOWcYDK82x

Count Injuries = 
SUMX (
    'Date',
        SUMX (
            Blad1,
            VAR CurrentDate = 'Date'[Date]
            RETURN
                IF ( 
                    Blad1[Injury_start] <= CurrentDate
                        && OR ( ISBLANK ( Blad1[Injury_end] ), Blad1[Injury_end] > CurrentDate ),
                    1,
                    0
                )
        )
)

1.png2.png

I feel so stupid. I tried for 30 minutes, but never seemed to do what I wanted. At the end I only had to delete the relation between the date table and injury table... Thanks a lot for the help! 

tamerj1
Super User
Super User

Hi @Maestro 

You have to have a date table. Then you may try

 

Count Injuries =
SUMX (
    'Date',
    VAR CurrentDate = 'Date'[Date]
    RETURN
        SUMX (
            injuries,
            injuries[start_injury] <= CurrentDate
                && OR ( ISBLANK ( injuries[stop_injury] ), injuries[stop_injury] > CurrentDate )
        )
)

 

It shows the error that the function SUMX cannot use the type "Boolean". Btw. I do have a date table, so that should not be the problem. 

@Maestro 
Apologies i missed the IF 

Count Injuries =
SUMX (
    'Date',
    VAR CurrentDate = 'Date'[Date]
    RETURN
        SUMX (
            injuries,
            IF (
                injuries[start_injury] <= CurrentDate
                    && OR ( ISBLANK ( injuries[stop_injury] ), injuries[stop_injury] > CurrentDate ),
                1,
                0
            )
        )
)

Hi,

 

Maybe try with : 

&& ( ISBLANK ( injuries[stop_injury] ) || injuries[stop_injury] > CurrentDate )
@tamerj1 why do you prefer a SUMX instead of COUNTX ?

@AilleryO 

Two reasons. I trust it more and it performs faster. However, can be much efficient using SUMMARIZE

Maestro
Frequent Visitor

I can show an outline of the table below if that works: EDIT: I use the DD-MM-YYYY format
- from the 28th of August it should show one injury, until 6th of September. Then it should show two injuries 10th of September. etc.

PlayerTeamSeasonStart injuryEnd injuryTime-lossLocationActivity
A1

2021-2022

28-8-202110-9-202113AnkleTraining
B12021-20226-9-202113-9-20215AnkleMatch
C22021-20229-9-202115-9-20216AnkleTraining
D32021-202213-9-202117-9-20214ThighMatch
E32021-202213-9-202124-9-202111KneeMatch

 

Currently I have two active tables: the one you see above and one date-table (with all possible variations of notations of dates from 2010 till 2025). This has a one-to-many relation with the injury table. 

Hi,

 

I add some datas to your dummy data to check, and this seems to work well :

 

Count of Injuries at date (mesure) = 

VAR DateCurrent = SELECTEDVALUE( TabInjuries[start injury] )

RETURN

CALCULATE( COUNT( TabInjuries[Player] ) , ALL( TabInjuries ) ,

TabInjuries[Start injury] <= DateCurrent &&

( TabInjuries[End injury] > DateCurrent || ISBLANK( TabInjuries[End injury] ) )

)
 
Gives that :
graf_injurie.png
 
tab injurie.png
In my table (screenshot) you can see two columns because I made calculation as well as column (depending on your needs).
 
 
 
 
 
 
 
 
 
 
 
 
 
Column will be :
Count of Injuries at date (column) = 

VAR DateCurrent = TabInjuries[start injury]

RETURN

CALCULATE( COUNT( TabInjuries[Player] ) , ALL( TabInjuries ) ,

TabInjuries[Start injury] <= DateCurrent &&

OR( TabInjuries[End injury] > DateCurrent , ISBLANK( TabInjuries[End injury] ) )

)
If that works on your model, do not forget to mark the post as Solved.
Thanks and enjoy

Tried both options, but it does not seem to replicate what I did in Excel. It seems to have too much deviations up and down (long injury does not count for the whole period). Maybe since the data is only added for any 'start of injury', but I am not sure. On the other side, since there are new injuries every week this should not make much of a difference 🤔.

 

I uploaded a bigger dataset, also suggested by @AilleryO  (not all true values, but it should provide enough data and 'real' information). Docs Spreadsheet (If I missed any private settings, please let me know 😅)

AilleryO
Memorable Member
Memorable Member

Hi,

 

Would be great if you could share some sample data (without any sensitive infos) using One Drive, Google Drive...

 

How many tables in your model and is one of them a Date table with a relationship to your injuries table ?

 

Let us know...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors