Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to Solution.
@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
)
)
)
@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
)
)
)
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!
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 ?
Two reasons. I trust it more and it performs faster. However, can be much efficient using SUMMARIZE
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.
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] ) )
)
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] ) )
)
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 😅)
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...
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
43 | |
31 | |
25 | |
22 | |
22 |