Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I'm building a dashboard that is supposed to give information about absentism and sick leaves in a company, but a problem arose:
I would like to be able to calculate how many people had active sick leaves during a concreate day (indicated by a slicer), but the way in wich the sick leaves are registred is the following:
Worker Tipology Start Date End Date
35 Sick leave 12/03/2018 02/04/2018
24 Sick leave 29/01/2018 07/05/2018
Beacuse there are not multiple registres for each day of the sick leave, but a single one containing the start and the end of it, I would need to create a measure that basically worked as following:
"CALCULATE (COUNTROWS (Absence); Absence[Tipology]="Sick leave"; Absence[Start Date]<=slicer date && Absence[End Date]>=slicer date)"
But I already know that this is not possible. Therefore, wich do you think would be the easiest way of obtaining how many people had active sick leaves during a concreate day (indicated by a slicer)?
Thankyou in advance and sorry for any language error (not a native speaker)!
Solved! Go to Solution.
Hi @GuillemXII,
Create a Dimesion date table and then add the following measure:
Sick_Leave_Count = VAR start_date = MIN ( DimDate[Date] ) RETURN CALCULATE ( COUNT ( Absent[Worker] ); FILTER ( ALL ( Absent[Start Date]; Absent[End Date] ); Absent[Start Date] <= start_date && Absent[End Date] >= start_date ) ) + 0
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello everyone,
First of all, sorry for the awful title, but I didn't found a better one.
I'm building a dashboard that is supposed to give information about absentism and sick leaves in a company, but a problem arose:
I would like to be able to calculate how many people had active sick leaves during a concreate day (indicated by a slicer), but the way in wich the sick leaves are registred is the following:
Worker Tipology Start Date End Date
35 Sick leave 12/03/2018 02/04/2018
24 Sick leave 29/01/2018 07/05/2018
Beacuse there are not multiple registres for each day of the sick leave, but a single one containing the start and the end of it, I would need to create a measure that basically worked as following:
"CALCULATE (COUNTROWS (Absence); Absence[Tipology]="Sick leave"; Absence[Start Date]<=slicer date && Absence[End Date]>=slicer date)"
But I already know that this is not possible. Therefore, which do you think would be the easiest way of obtaining how many people had active sick leaves during a concreate day (indicated by a slicer)?
Thankyou in advance and sorry for any language mistake (I'm not a native speaker)!
Hi @GuillemXII,
Create a Dimesion date table and then add the following measure:
Sick_Leave_Count = VAR start_date = MIN ( DimDate[Date] ) RETURN CALCULATE ( COUNT ( Absent[Worker] ); FILTER ( ALL ( Absent[Start Date]; Absent[End Date] ); Absent[Start Date] <= start_date && Absent[End Date] >= start_date ) ) + 0
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you very much, it worked perfectly!
However, could you explain me how the "VAR"+"RETURN" work? I've never used those comands and I don't understand what they are doing here...
VAR start_date = MIN ( DimDate[Date] ) RETURN
Hi @GuillemXII,
When you create calculated measures or columns you can setup difference variables (VAR) in order to simplify your measure, so when youn have a recurring calculation that you need to use several times in your measure you can create a variable or several, and use it instead of having to write the same code over and over again.
The Return part is the part where you define the start of your result calculation.
Check this link where you have an explanation of the use of variables in DAX.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |