Conditional Formatting on a Matrix & More
I've been stuck on a problem for my work for a while. I've made progress, but I can't seem to find a prooper solution to my conditional formatting issue. In Microsoft Power BI, I want to setup a matrix visual to show when the Doc Num of a Contract is New, Open, or Closed using Conditional Formatting. This will be over the course of different Verisons of Data pulls, and separated in different Unit groups. I want to ensure that I can adjust the DAX so that Closed only appears after a Doc Num disappears (e.g. not showing Closed in the same version it last appears) My current table has 3 columns: Doc Num, String Version, Decimal number Unit, String Lastly, I want to apply the conditional formatting to the matrix itself. This is because for each version, the Doc Num may change from New, to Open to Closed. Here's my current code: ContDocument Status = VAR _CurrentVersion = SELECTEDVALUE(DataPage_Tables_Append1[Version]) VAR _DocNum = SELECTEDVALUE(DataPage_Tables_Append1[DAC Doc Num]) VAR _CurrentUnit = SELECTEDVALUE(DataPage_Tables_Append1[Unit]) VAR _LastVersion = CALCULATE( MAX(DataPage_Tables_Append1[Version]), FILTER( ALL(DataPage_Tables_Append1), DataPage_Tables_Append1[DAC Doc Num] = _DocNum && DataPage_Tables_Append1[Unit] = _CurrentUnit ) ) VAR _FirstVersion = CALCULATE( MIN(DataPage_Tables_Append1[Version]), FILTER( ALL(DataPage_Tables_Append1), DataPage_Tables_Append1[DAC Doc Num] = _DocNum && DataPage_Tables_Append1[Unit] = _CurrentUnit ) ) RETURN SWITCH( TRUE(), _CurrentVersion = _FirstVersion, "New", _CurrentVersion > _FirstVersion && _CurrentVersion < _LastVersion, "Open", _CurrentVersion = _LastVersion, "Closed", BLANK() ) This is what the Output currently looks like: The problem is, that almost all Doc Nums are in Closed status at the last Version (unless the Doc Num is New during that Version), and that is not actually true. Some of those Doc Nums should be in Open status because it is not confirmed that the Doc Num disappeared. Another functionality that I want to add to my matrix, is to have a Doc Num be reopened in case there is an edit, and the Doc Num must be revisied. I do not know how to do this. Any guidance on this issue would be appreciated. Thank you in advance! Best, tkerr98
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 )
Help Needed: DAX Measure to determine if SLA reached the target or not.
Hi Everyone, I'd like to ask for your help. I have data with different targets per SLA. Minimum column is also target but it's the minimum % an SLA must have. I created a measure that I will use to create an icon (using conditional format) to determine if the SLA Summary results met the target or not. _ConditionSLATarget = MAXX('Monthly Summary', IF('Monthly Summary'[_SLA Summary Results] >= 'Monthly Summary'[Target],1, IF('Monthly Summary'[_SLA Summary Results] >= 'Monthly Summary'[Minimum],0, IF('Monthly Summary'[_SLA Summary Results] < 'Monthly Summary'[Minimum],-1)))) ----------------------------------------------------------------------------------------------------- My Table is: 'Monthly Summary' SLA measure is _SLA Summary Results DAX Formula for _SLA Summary Results below: _SLA Summary Results = SUM('Monthly Summary'[Met])/SUM('Monthly Summary'[Volume]) However, there's something strange when I put it in a table. If you notice, DAT 1 SLA should be "-1" with a downward arrow because SLA Summary result of 85.32% did not meet the minimum target of 96% I don't know what went wrong so I'd like to ask for your expertise on this matter. Let me know if you need additional info. Thank you Here's a screenshot of my conditional format for the icons in case you need.
