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

Resolver I

## Use SUMMARIZE not as calculated table but in a measure

I have a simple star scheme (dimdate, dimemployee, factworkschedule).
In factworkschedule I have 3columns: EmployeeID, workdate, Activitytype (1 = working, 2 = break etc. 9 = Sickleave). For this question only 1 (working day) and 9 (sickleave day) are relevant.
I want to calculate the sickleave% for the whole company (so all employees for the whole month).

I can succesfully do this using a calculated table with SUMMARIZE (table DAX calculated table , see example attached), creating 2 additional relations in the model, and then measure (Sickleave rate) that uses this calculated table.

I dont like the additional calculated table, I only want to use measure(s). How can I do that?

Note that my solution uses SUMMARIZE, but this is not required (use what you think is best).

The end result should be a % with the  sickleave days / workdays, and should be visible per week/day/month and employee/whole company.

See below picture, so I dont want the calculated table and the 2 relations.

See here my datamodel on my Dropbox account: https://www.dropbox.com/s/q3m3zm0b4tzds8o/Sickleave%20rate.pbix?dl=0

Filip

1 ACCEPTED SOLUTION
Super User

You can use SUMMARIZE within a measure definition but I don't think its necessary in this situation. Try

`Sick Leave % = var sickDays = CALCULATE( COUNTROWS( Workschedule), Workschedule[activitytypeid] = 9 )var totalDays = CALCULATE( COUNTROWS( Workschedule), Workschedule[activitytypeid] IN {1, 9} )RETURN DIVIDE( sickDays, totalDays)`
2 REPLIES 2
Resolver I

Thanks Johnt75,

This is a clean/fast solution!

Regards Filip

Super User

You can use SUMMARIZE within a measure definition but I don't think its necessary in this situation. Try

`Sick Leave % = var sickDays = CALCULATE( COUNTROWS( Workschedule), Workschedule[activitytypeid] = 9 )var totalDays = CALCULATE( COUNTROWS( Workschedule), Workschedule[activitytypeid] IN {1, 9} )RETURN DIVIDE( sickDays, totalDays)`

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.