Calculate percentage of employees not exceeding 5 consecutive days per week, with measures only
I am hitting a wall trying to create a measure that calculates the number of employees not exceeding 5 days worked consecutively per week (defined as Monday - Sunday, not a 7 day period). So far I have only been able to achieve it by first creating a calculated table with a summary of dates, employee id's, year_week and year_month, then some additional calculated columns and finally two measures. The issue with this is that since I am pulling the employee id's from the employee table and the dates, year_month and year_week from the date table, I cannot visualize it on a table visual that uses any date field (in my case I use year_month), or use any of my dimension table slicers related in any way to th date or employee table, and I cannot create a relationship back to the tables as it creates circular references. My initial table: FiveDaysWorkedInArowPerWeek = SUMMARIZECOLUMNS( 'm d_employees'[employee_id], 'm d_date'[date_date], 'm d_date'[year_week], 'm d_date'[year_month], FILTER( 'm d_employees', [Scheduled Hours from daily table] > 0 ), "scheduled hours", [Scheduled Hours from daily table] ) Then I have these calculated columns: Index = VAR CurrentEMPLOYEEID = FiveDaysWorkedInArowPerWeek[employee_id] VAR CurrentYearWeek = FiveDaysWorkedInArowPerWeek[year_week] RETURN RANKX( FILTER( ALL(FiveDaysWorkedInArowPerWeek), FiveDaysWorkedInArowPerWeek[employee_id] = CurrentEMPLOYEEID && FiveDaysWorkedInArowPerWeek[year_week] = CurrentYearWeek ), FiveDaysWorkedInArowPerWeek[date_date], , ASC, Dense ) This just creates a basic index of all rows per employee and week. I.e. If the first day of the week is monday, they it gets 1, and if the last day is friday but wednesday was off, friday gets 4 as there were only 4 entries for that week. Consecutive Days Initial = VAR CurrentEMPLOYEEID = FiveDaysWorkedInArowPerWeek[employee_id] VAR CurrentYearWeek = FiveDaysWorkedInArowPerWeek[year_week] VAR CurrentDate = FiveDaysWorkedInArowPerWeek[date_date] VAR CurrentIndex = FiveDaysWorkedInArowPerWeek[Index] VAR PreviousDate = CALCULATE( MAX(FiveDaysWorkedInArowPerWeek[date_date]), FILTER( ALLEXCEPT(FiveDaysWorkedInArowPerWeek, FiveDaysWorkedInArowPerWeek[employee_id], FiveDaysWorkedInArowPerWeek[year_week]), FiveDaysWorkedInArowPerWeek[date_date] < CurrentDate && FiveDaysWorkedInArowPerWeek[Index] = CurrentIndex - 1 ) ) VAR DaysDifference = DATEDIFF(PreviousDate, CurrentDate, DAY) RETURN IF( CurrentIndex = 1 || DaysDifference > 1, 1, 0 ) Here the output gives a 1 if the current index is 1, i.e. the first day worked in a week / the first entry in a week, for an employee, and then it gives a 1 if the date is bigger than the previous date by more than 1 day. I.e: employee_id year_week date_date Index Consecutive days initital 1238574 202323 2023-06-05 (mon) 1 1 1238574 202323 2023-06-06 (tue) 2 0 1238574 202323 2023-06-07 (wed) 3 0 1238574 202323 2023-06-10 (sat) 4 1 1238574 202323 2023-06-11 (sun) 5 0 1238574 202324 2023-06-12 (mon) 1 1 1238574 202324 2023-06-13 (tue) 2 0 1238574 202324 2023-06-14 (wed) 3 0 1238574 202324 2023-06-16 (fri) 4 1 1238574 202324 2023-06-18 (sun) 5 1 1238574 202325 2023-06-24 (sat) 1 1 1238574 202326 2023-06-26 (mon) 1 1 1238574 202326 2023-06-27 (tue) 2 0 1238574 202326 2023-06-28 (wed) 3 0 1238574 202326 2023-06-29 (thu) 4 0 Consecutive Days = VAR CurrentEMPLOYEEID = FiveDaysWorkedInArowPerWeek[employee_id] VAR CurrentYearWeek = FiveDaysWorkedInArowPerWeek[year_week] VAR CurrentIndex = FiveDaysWorkedInArowPerWeek[Index] VAR ConsecutiveStart = CALCULATE( MAX(FiveDaysWorkedInArowPerWeek[Index]), FILTER( FiveDaysWorkedInArowPerWeek, FiveDaysWorkedInArowPerWeek[employee_id] = CurrentEMPLOYEEID && FiveDaysWorkedInArowPerWeek[year_week] = CurrentYearWeek && FiveDaysWorkedInArowPerWeek[Index] <= CurrentIndex && FiveDaysWorkedInArowPerWeek[Consecutive Days Initial] = 1 ) ) RETURN CurrentIndex - ConsecutiveStart + 1 MaxConsecutiveDaysPerWeek = CALCULATE( MAX(FiveDaysWorkedInArowPerWeek[Consecutive Days]), ALLEXCEPT(FiveDaysWorkedInArowPerWeek, FiveDaysWorkedInArowPerWeek[employee_id], FiveDaysWorkedInArowPerWeek[year_week]) ) Finally, here I sort the correct/intended indexing, i.e. it counts from 1 and up within any given week for an employee until it is a new week OR the previous day within a week for an employee is greater than one day apart. I.e. employee_id year_week date_date Index Consecutive days initital Consecutive days 1238574 202323 2023-06-05 (mon) 1 1 1 1238574 202323 2023-06-06 (tue) 2 0 2 1238574 202323 2023-06-07 (wed) 3 0 3 1238574 202323 2023-06-10 (sat) 4 1 1 1238574 202323 2023-06-11 (sun) 5 0 2 1238574 202324 2023-06-12 (mon) 1 1 1 1238574 202324 2023-06-13 (tue) 2 0 2 1238574 202324 2023-06-14 (wed) 3 0 3 1238574 202324 2023-06-16 (fri) 4 1 1 1238574 202324 2023-06-18 (sun) 5 1 1 1238574 202325 2023-06-24 (sat) 1 1 1 1238574 202326 2023-06-26 (mon) 1 1 1 1238574 202326 2023-06-27 (tue) 2 0 2 1238574 202326 2023-06-28 (wed) 3 0 3 1238574 202326 2023-06-29 (thu) 4 0 4 Now that I have the Consecutive Days column, I can go ahead and calculate the percentage of employees (that has any worktime), for each week, that does not exceed 5 (notice I just flip the percentage by subtracting from 1 in the return statement): PercentageFiveDaysPerWeek = VAR EmployeesFiveDaysOnly = CALCULATE( DISTINCTCOUNT(FiveDaysWorkedInArowPerWeek[employee_id]), FiveDaysWorkedInArowPerWeek[MaxConsecutiveDaysPerWeek] > 5 ) VAR TotalEmployees = [TotalEmployeesPerWeek] RETURN CALCULATE(1-DIVIDE(EmployeesFiveDaysOnly, TotalEmployees, 0)) And then I get to my final measure, where I calculate how many employees do not exceed 5 consecutive days per week, each month (as I am presenting per month): PercentageFiveDaysPerMonth = AVERAGEX( SUMMARIZE( FiveDaysWorkedInArowPerWeek, FiveDaysWorkedInArowPerWeek[year_month], "Percentage", [PercentageFiveDaysPerWeek] ), [Percentage] ) Again, this works fine on its own if I only want to output it in the table using the year_month column from the calculated table called 'FiveDaysWorkedInArowPerWeek'. However, since there are no relationships and they cannot be created due to circular dependencies, I cannot add slicers of any other dimension table on the page, or use the proper year_month column (the one from my 'm d_date' table). So users will not be able to filter on the location dimension connected to the employees table, not on the contract type of the employee, not on the account they belong to, not on any other attributes like WAH/Brick and Mortar, if they are temporary or permanent employees, etc. etc. I have other measures that work fine, but in this case I could not wrap my head around the complexity of this in order to make it into a series of measures that work dynamically with my dimension tables (i.e. the original employee table and the original date table). Note that the [Scheduled Hours from daily table] measure in the initial table calculation works dynamically wherever it lives as it just sums up time from my schedule table, which has relationships to both the employee and date table. Anyone have any suggestions?
DAX Measure based on multiple fields
Hi, I am trying structure my calculations using DAX and i cannot change my datamodel. I need to calculate two measures Rating Measure - this is calculated by considering sum of above/ sum of under from format column when setting is Rating Target Measure- this is calculated by considering sum of above/ sum of under from format column when setting is Target I achieved this calculation in powerquery but i wanted to use DAX for other reasons. Can you help in providing tips to achieve these measures? Date Product Format Setting Value Jan-23 Lux Above Rating 20 Jan-23 Lux Under Rating 30 Jan-23 Lux Above Target 30 Jan-23 Lux Under Target 40 Feb-23 Lux Above Rating 30 Feb-23 Lux Under Rating 40 Feb-23 Lux Above Target 40 Feb-23 Lux Under Target 50 Mar-23 Lux Above Rating 45 Mar-23 Lux Under Rating 45 Mar-23 Lux Above Target 40 Mar-23 Lux Under Target 50 Expected solution for rating and target measure: Thanks for your help
Month on Month Growth % in the given table
I have this table as below and I have dervied month_number from the Month_year column. I want to calculate the MOM growth on Online_Traffic in calculated column which will tell me what is the % compared last month. How Do I achieve this with dax. please help me in this.Calculating median of time by group
Hi. Im having trouble in calculating the median of a column in my proyect. I can't get it to work (no result). Well, the thing it's like this. I have 2 tables, one it's a calendar table and the other one it's my data table. They have a one to many relation. I want to get the median of a column, group by date (month date) like i have my other measures, but i cant get it to work. The values that i want to get the median, are expresed in seconds. So, what i want should be something like this: And it should be able to filter by Consulta1[CANAL] I tried creating a measure in many ways, median (time_total) = MEDIAN(tablaPortabilidades[TPO_TOTAL]) median (time_total) = MEDIANX(tablaPortabilidades, tablaPortabilidades[TPO_ACEPT]) I also tried creating a measure that sums [TPO_ACEPT] and then creating a median of this sum, but same result. The result: Can someone give me a hand? I don't know how to upload my .pbix here so i upload it to my drive. .pbix Here the data was imported, but im usind a direct query connection. Pd: In the pbix the Consulta1 is the same as tablaPortabilidades Thanks!DAX to get SUM of columns above a certain record
Hello - I am looking for a DAX measure that sums up the duration minutes for those records before and after the "Changeover" record. So I am looking for a measure that would be 25.91 (sums up the first two records for Producing and Discharge Backup prior to the "Changeover" record) and then another measure that would be 7.92 which is the Producing record after the Changeover one. I'm trying different stuff but can't quite get how to find the changeover record and then go from there. Any help would be awesome. Thank you!Swicth Function outcome used as slicer
Hi guys, I made a switch function that gives me the status of our machines -> Threshold Last Audit = SWITCH(TRUE(),[DateDiff Last Audit]<=14,"Green", [DateDiff Last Audit]<=39,"Amber",[DateDiff Last Audit]>=40,"Red") This works fine when i drag the serial number of the machines into the visual. However, i would like to make a slicer in which i would display "Green", "Amber" and "Red" as options to be picked, and I am stuck there. As we use direct query in our company, i cannot create a seperate table, neither add calculated columns. Does anybody know how i can solve my issue using calculated measures only? Thanks!
