Calculated Column To Determine Attendance Status
I am building an attendance dashboard and already integrated holidays and weekends and a calculated column that calculated the durantion between the earliest entry and latest exit timestamps and everything checks out except for the "Attendance Status" calculated column where I am using the SWITCH function with all possible conditions but I am not getting the expected results as can be seen in the screenshot, when the day is "Regular" and the completed hours are equal or more than 8 then it should be fulfilled otherwise it is incomplete same with ramadan but the hours being 5 instead but I "Absent" and "Not Required" are the only results I am getting even when that is not the case. I tried to test out each condition individualy to troubleshoot but to no avail. Below is the dax expression I used: Attendance Status = SWITCH( TRUE(), ISBLANK([Completed_Hours]) && RELATED(Calendar[DayType]) IN {"Weekend", "Holiday"}, "Not Required", ISBLANK([Completed_Hours]) && RELATED(Calendar[DayType]) = "Regular", "Absent", ISBLANK([Completed_Hours]) && RELATED(Calendar[DayType]) = "Ramadan", "Absent", RELATED(Calendar[DayType]) = "Regular" && [Completed_Hours] >= 8, "Fulfilled", RELATED(Calendar[DayType]) = "Regular" && [Completed_Hours] < 8, "Incomplete", RELATED(Calendar[DayType]) = "Ramadan" && [Completed_Hours] >= 5, "Fulfilled", RELATED(Calendar[DayType]) = "Ramadan" && [Completed_Hours] < 5, "Incomplete", RELATED(Calendar[DayType]) IN {"Weekend", "Holiday"} && [Completed_Hours] >= 8, "Fulfilled", RELATED(Calendar[DayType]) IN {"Weekend", "Holiday"} && [Completed_Hours] < 8, "Incomplete", "Unknown" )
Using a switch statement to select a column
I am completely stumped. I'm trying to use… a slicer to select a single value (this works) Then use that value with a SWITCH statement to select a column (this doesn't work) How I've set this up... I have a table called 'Slicer_Table' This table has one column called 'Slicer_Options' The column contains two twos and two values 1 & 2. I have a second table called 'Data_Table'. This table has two columns, called 'Column_1' and 'Column_2'. 'Column_1' contains the values A, B, C, D, E, F, G 'Column_2' contains the values H, I, J, K, L, M, N In the dashboard I have a slicer. Which uses 'Slicer_Table->Slicer_Options' as it's field value. It has the style Tile & has the selection 'Single Select' 'On'. In the dashboard I have a measure called 'Select_Column' Associated with the 'Data_Table'. This measure (doesn't) work by… Taking the currently selected slicer value, and storing it in the VAR SelectedColumn e.g., VAR SelectedColumn = SELECTEDVALUE(Slicer_Table[Slicer_Options]) Using 'SelectedColumn' in a SWITCH statement to return Data_Table[Column_1] or Data_Table[Column_2] Depending on the value of 'SelectedColumn' e.g., SWITCH( SelectedColumn, 1, Data_Table[Column_1], 2, Data_Table[Column_2] ) The measure as a whole looks like: Select_Column = VAR SelectedColumn = SELECTEDVALUE(Slicer_Table[Slicer_Options]) RETURN SWITCH( SelectedColumn, 1, Data_Table[Column_1], 2, Data_Table[Column_2] ) However, this doesn't work. No matter what the value of 'SelectedColumn' Nothing is returned by the SWITCH statement. If I replace 'SelectedColumn' with a constant value e.g., Select_Column = VAR SelectedColumn = SELECTEDVALUE(Slicer_Table[Slicer_Options]) RETURN SWITCH( 1, 1, Data_Table[Column_1], 2, Data_Table[Column_2] ) Then it does work - Setting the value to 1 returns Data_Table[Column_1] Setting the value to 2 returns Data_Table[Column_2] as I would expect. That it works with constant values, makes me think this should be feasible with a variable value. & that I am doing something wrong - I'm just not sure what?
Struggling to create a calculation that uses dates from different tables
Hi, I have a problem where my volume sold is duplicating. This is because some SKU's have 2 recipes - the recipe has changed slightly but it is the same product. We have dates for when these recipes are valid and what I am trying to do is pull back the details of the recipe that is valid at a given time along with the volume for that month. I have created a measure for this - when the month is between the launch date and the discontinued date then "Yes" else "No". That appears to have worked. Then it's a simple case of filtering when Correct Recipe = "Yes". I get what I expect when I do this. However, the problem I have is as the recipe dates and Full Date (month of sale) are in separate tables I had to use the max() function in my measure: Correct Recipe = switch(true(), max(Financial_Data[Full Date]) >= max('OPOH NUTRITION'[Launch Month]) && MAX(Financial_Data[Full Date]) < MAX('OPOH NUTRITION'[Discontinued Month]), "Yes", "No") I want a card with just the volume sold on it, but as my measure uses the max fucntion() it just takes the max launch date etc. overall and does not give me the figures I need. I have also tried to create a column/measure that says if the order month is between the launch and discontinued dates then "Volume" else 0 and then I was planning to use SUMX(). But again I'm struggling to references dates from different tables in the same calculation. Does anyone know how to get around this please? Thanks MichaelTotal Row of Measure not summing as expected
Hi, We have to pay the council PRN costs for the materials we use in our packaging. We pay this price per case sold, so Cases * PRN Price per case = PRN Costs to pay. These costs changed in April 2023, so what I want to do is for anything before April 2023 I want to do “Volume (cases)” x “PRN Cost per case before” and anything after April 2023 I want to do “Volume (cases)” x “PRN Cost per case after”. For every row I have created a column that calculated the cost using the price before April 2023 (PRN Cost v4 before) and another column looking at the cost using the price after April 2023 (PRN Cost v4 after). I have then created the measure PRNv5. This measure is a switch statement that says if the date is before April 2023 then use the value from “PRN Cost v4 before” and if the date is after April 2023 then use the value from “PRN Cost v4 after” This measure appears to work at the row level however tht Total Row is not equal to the sum of the PRNv5 Column. £249.50 + £167.64 is £417.14, not £426.50 as the Total row suggests. I can't use the sumx() function as it is a measure. However I can't seem to create PRNv5 as a column at all. Any help would be much appreciated! Thanks MichaelNeed Help with DAX 2-Day Lag Formula return different data sources based on future vs. past
Hi, I am trying to create a DAX formula that will return hours depending on the day. Please help! I am very stuck. For past dates before TODAY and YESTERDAY, I want to return the data from "Actual Hours" measure/column. For TODAY, YESTERDAY and FUTURE DAYS, I want to return the data from "Scheduled Hours" measure/column. I am currently using the below formula but it is not bringing in "Scheduled Hours" for today and future. The hours return for past dates before Yesterday and Today are incorrect. I am comparing this data in a table matrix by bringing in Actual Hours and Scheduled Hours side by side. 2 Day Lag Hours = VAR CurrentDate = SELECTEDVALUE('Tables- Dates'[Date]) VAR Result = SWITCH( TRUE(), CurrentDate < TODAY() - 1,[Actual Hours], //Past: Actual Hours Measure CurrentDate <= TODAY(), [STAR Scheduled Hours], //Today and Yesterday: Scheduled Hours Measure CurrentDate > TODAY(), [STAR Scheduled Hours] //Future: Scheduled Hours Measure ) RETURN Result Thank you!
