Slow DAX formula to calculate # Orders Delayed with adjustment
Hi I have a datamodel with orderlines and goods receipt. I have written a formula to calculate number of unique orders delayed (in Danish # Ordre Forsinket) based on two what if parameters. The parameter 'Leveringstid Regionslager rabat'[Leveringstid Regionslager rabat] is used to adjust the delay with X number of days on those orders there are marked as indkøbsbilagsart ZCD or ZLM. The parameter 'Leveringstid Øvrige rabat'[Leveringstid Øvrige rabat] is used to adjust the delay with X number of days on those orders there are different from indkøbsbilagsart ZCD or ZLM. The data model looks like this and there is 1.4 million rows in the table 'Ordrelinjer' and 2.775 suppliers in the table 'Leverandør': Here is the DAX formula: # Ordre Forsinket = VAR _LeveringstidRegionslagerRabat = SELECTEDVALUE( 'Leveringstid Regionslager rabat'[Leveringstid Regionslager rabat], 0 ) VAR _LeveringstidOevrigeRabat = SELECTEDVALUE( 'Leveringstid Øvrige rabat'[Leveringstid Øvrige rabat], 0 ) RETURN CALCULATE( DISTINCTCOUNT('Ordrelinjer'[Indkøbsordrenummer]), FILTER( ADDCOLUMNS( FILTER( 'Ordrelinjer', 'Ordrelinjer'[Varemodtagelse] = 1 && 'Ordrelinjer'[Afvigelse i dage] > 0 ), "JusteretAfvigelse", 'Ordrelinjer'[Afvigelse i dage] - IF( RELATED('Indkøbsbilagsart'[Indkøbsbilagsart]) IN {"ZCD", "ZLM"}, _LeveringstidRegionslagerRabat, _LeveringstidOevrigeRabat ) ), [JusteretAfvigelse] > 0 ) ) The measure is used in a table visual, but it takes around 33 seconds to refresh the table visual. I need a few more measures in the table, so it will just take longer and longer time to refresh the table. Can I write the DAX formula in a more efficient, clean and performance optimized way? Best regards Morten
Show YTD Months in Column (Matrix) based selected Month in Slicer
Hi , I need to create Matrix visual like below based on Month-Year slicer selection need to display YTD Ex:1 Slicer selection is Feb-24 then show as below Ex:2 Slicer selection is Dec-23 then Jan-23 to Dec-23 to show in column I have tried some logic its working in table but not in Matrix Thanks, Sanjay
Why is Count and Distinctcount together with Calculate giving a negative number?
I'm writing some formulas that use the Count or Distinctcountnoblank functions, but when I combine them with Calculate to filter based on a condition in the data, it returns a negative number for some of the measures but not all. Whether it's positive or negative seems to vary with the condition applied. See below for an example where I'm trying to output the count of call_id whose call type is "123". How is it even possible for this to return a negative number? How do I make it work properly without having to do a series of IF/THEN clauses? Measure Example = CALCULATE(DISTINCTCOUNTNOBLANK(table[call_id]), table[type]="123")Filtering Rows with no value in multiple columns
Hi. May I ask your help please how to create the DAX of my scenario. In the table visual, I want only to show Project T and Y because they have no values from Q1 to Entire Duration columns. So If I use the Department Manager slicer and select DM1 , I want to show only Project T and Y. Columns 'ID' and 'BOP Title' is in another table, then 'Department column up to 'Entire Duration' is also another table in my data model. The BOP ID column was used to create relationship for the two tables. Thank you for your help Thanks, Third
DAX Calculated Column Creation for Inventory Tracking
Hi everyone, I'm currently facing some difficulty in generating a calculated column using DAX, whereas it's relatively straightforward using Excel. 1. Below is an image displaying a sample of the dataset I'm currently working with: This dataset illustrates the inventory of two products. I'm attempting to monitor the remaining units after each shipment. 2. The new column, named "Quantity," is what I'm aiming to create using DAX. 3. Here is the simple Excel formula that achieves the desired output: The logic needs to take into consideration there multiple items within Category, so it can't simply substract the shipment/ or add the stock replenishment from the above value, this is where I suspect the CategoryIndex will come in handy. Ideally, I would prefer a solution in DAX. However, if it can be accomplished using M Code, I'm also open to suggestions.
Determine Average Employee Count for each year & Count Total Terminations, to Derive Turnover %
Hi Wonderful People, I need help creating a rolling Turnover % Calculation for our HR Team. For Each Year and Month, The average number of Active Staff we had for each year, And the Total Terminations for Each Year, So I can use these two variables, to calculate the Turnover %. This way, i can use a line chart with Year-Month & the Turnover % and Drill down by Division. I have Three Tables modelled below. 1.) Employee Data Table = Employee ID | Hire Date | Termination Date | Division. (this table has all employees for the past 8 Years) 2.) Date Table 1 = Connected to Hire Date 3.) Date Table 2 = Connected to Termination Date. My current Rolling Head Count Calculation= This calculation appears to be correct. " CALCULATE(DISTINCTCOUNT(ADP_CurrentActive[EMP_CODE]), FILTER(ADP_CurrentActive,ADP_CurrentActive[Termination Reason]<>"Employee did not commence"), FILTER( VALUES( 'ADP_CurrentActive'[Hire Date]), ADP_CurrentActive[Hire Date] <= MAX( DATETABLE_DIM[Date] ) ), FILTER( VALUES( ADP_CurrentActive[TERM_DATE]), OR( 'ADP_CurrentActive'[TERM_DATE] >= MIN( DATETABLE_DIM[Date]), ISBLANK( 'ADP_CurrentActive'[TERM_DATE])))) " If the Employee is not Terminated- I fill in the Blank Term Date with a Static Date of 01/01/2050. All Terminations Calculation = This Calculation is correct when i validate. " CALCULATE(DISTINCTCOUNT(ADP_CurrentActive[EMP_CODE]), FILTER(ADP_CurrentActive,ADP_CurrentActive[TERM_DATE]<>01/01/2050), USERELATIONSHIP(ADP_CurrentActive[TERM_DATE],TERMDATETABLE_DIM[Date])) What do you think would be the best approach to this. I think it would be best to create a Custom Table, for each Year/Month, with Total Staff at End of Year, and Total Terminations at End of Year, then add a calculated column from there to determine the Turnover %. But i do not know how to do this 😞 Thank you
PRIORITY Number Change and create new column based which number in first position instead one
I have a dataset with two columns. If the "Priority" column has a value of 1, it should be changed to any other number, for example, if it's changed to 3, then 1 will be changed to 3, and 3 will be changed to 1. I have attached a sample data with the desired output. TASKNO PRIORITY 1 1 2 2 3 3 4 4 5 5 IF PRIORITY ORDER IF I CHANGE 1 TO 5 .. I WANT OUTPUT LIKE TASKNO PRIORITY TO CHANGE 1 1 5 2 2 2 3 3 3 4 4 4 5 5 1 IF PRIORITY ORDER IF I CHANGE 1 TO 3 .. I WANT OUTPUT LIKE TASKNO PRIORITY TO CHANGE 1 1 3 2 2 2 3 3 1 4 4 4 5 5 5 IF PRIORITY ORDER IF I CHANGE 1 TO 4 .. I WANT OUTPUT LIKE TASKNO PRIORITY TO CHANGE 1 1 4 2 2 2 3 3 3 4 4 1 5 5 5 Thanks in advance .Counting Weekends Where Friday and Monday Are Sick Days
Hi I'm currently working on a formula to count sick days for employees, specifically considering weekends where both Friday and Monday are marked as sick days. However, I'm encountering some issues with the formula, and I'd greatly appreciate your expertise and insights. The purpose of the formula is to: Count sick days when an employee is absent for the entire day or more. Accurately identify consecutive weekends when both Friday and Monday are sick days and add them to the count. Unfortunately, the current formula isn't functioning as expected, and the weekends aren't being counted correctly. I've tried debugging the formula and making adjustments, but I'm still facing challenges. Please help me figure oth how to counts the weekends prpoerly! SickDays = VAR EmpNo = F_Illness[Emp No] VAR IllnessHours = F_Illness[Illness Hours] VAR CurrentDate = RELATED(D_date[Date]) VAR DayOfWeek = WEEKDAY(CurrentDate, 2) -- 1 = Monday, 2 = Tuesday, ..., 7 = Sunday VAR WorkHoursOnCurrentDay = CALCULATE( MAX(D_WorkSchedule[Hours]), D_WorkSchedule[Day] = SWITCH( DayOfWeek, 1, "Monday", 2, "Tuesday", 3, "Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday", 7, "Sunday" ) ) VAR NextDate = CurrentDate + 1 VAR PreviousDate = CurrentDate - 1 // Check if the previous day is Saturday and the day before is Sunday (consecutive weekends) VAR IsPreviousConsecutiveWeekend = IF( DayOfWeek = 7 && WEEKDAY(PreviousDate, 2) = 6, -- Sunday and Saturday CALCULATE( SUM(F_Illness[Illness Hours]), F_Illness[Emp No] = EmpNo, F_Illness[Date] = PreviousDate ) >= WorkHoursOnCurrentDay, -- Full sick day on Friday FALSE() ) // Check if the next day is Sunday and the day after is Monday (consecutive weekends) VAR IsConsecutiveWeekend = IF( DayOfWeek = 6 && WEEKDAY(NextDate, 2) = 7, -- Saturday and Sunday CALCULATE( SUM(F_Illness[Illness Hours]), F_Illness[Emp No] = EmpNo, F_Illness[Date] = NextDate ) >= WorkHoursOnCurrentDay, -- Full sick day on Monday FALSE() ) RETURN IF( IllnessHours >= WorkHoursOnCurrentDay || IsConsecutiveWeekend || IsPreviousConsecutiveWeekend || (IsConsecutiveWeekend && IsPreviousConsecutiveWeekend), -- Check for both consecutive weekends 1, 0 )
