cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

I 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
Super User

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

``````Count Injuries =
SUMX (
'Date',
SUMX (
VAR CurrentDate = 'Date'[Date]
RETURN
IF (
1,
0
)
)
)``````

11 REPLIES 11
Super User

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

``````Count Injuries =
SUMX (
'Date',
SUMX (
VAR CurrentDate = 'Date'[Date]
RETURN
IF (
1,
0
)
)
)``````

Frequent Visitor

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!

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

Frequent Visitor

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.

Super User

@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
)
)
)``````
Memorable Member

Hi,

Maybe try with :

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

Super User

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

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.

 Player Team Season Start injury End injury Time-loss Location Activity A 1 2021-2022 28-8-2021 10-9-2021 13 Ankle Training B 1 2021-2022 6-9-2021 13-9-2021 5 Ankle Match C 2 2021-2022 9-9-2021 15-9-2021 6 Ankle Training D 3 2021-2022 13-9-2021 17-9-2021 4 Thigh Match E 3 2021-2022 13-9-2021 24-9-2021 11 Knee Match

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.

Memorable Member

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] )RETURNCALCULATE( COUNT( TabInjuries[Player] ) , ALL( TabInjuries ) , TabInjuries[Start injury] <= DateCurrent && ( TabInjuries[End injury] > DateCurrent || ISBLANK( TabInjuries[End injury] ) ))`

Gives that :

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]RETURNCALCULATE( 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
Frequent Visitor

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

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...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors