hi I have a table as below:
I would like to create a column and find 1st break, 2nd break, and lunch.
if an agent on the same day, the aux name is Break and Duration is larger than 600. the first break will be the Break1, the second break will be the Break2.
if an agent on the same day, the aux name is Lunch and Duration is larger than 1000, it will be Lunch.
How do I perform this in Power BI DAX? I try couple time but it didn't work.
Here is my dataset in table:
Table
thanks
I would like to create a column and find 1st break, 2nd break, and lunch.
if an agent on the same day, the aux name is Break and Duration is larger than 600. the first break will be the Break1, the second break will be the Break2.
if an agent on the same day, the aux name is Lunch and Duration is larger than 1000, it will be Lunch.
How do I perform this in Power BI DAX? I try couple time but it didn't work.
first Break =
VAR EmployeeDateBreaks =
FILTER(
'Adherence and Deviation',
[Employee Name] = EARLIER([Employee Name]) &&
[Date] = EARLIER([Date]) &&
[Aux Name] = "Break" &&
[Duration] > 600
)
RETURN
IF(
COUNTROWS(EmployeeDateBreaks) > 0 &&
RANKX(EmployeeDateBreaks, [Aux Start], , ASC) = 1,
"Break1",
BLANK()
)
Test.B1.ACTUAL =
VAR CurrentDate = 'Adherence and Deviation'[Date]
VAR CurrentEmployee = 'Adherence and Deviation'[Employee Name]
VAR FilteredTable = FILTER('Adherence and Deviation',[Date] = CurrentDate && [Employee Name] = CurrentEmployee &&
[Aux Name] = "break" &&
[Duration] >= 600)
VAR SecondBreakRow = ADDCOLUMNS ( FilteredTable, "Rank",
RANKX(FILTER('Adherence and Deviation', [Date] = CurrentDate &&
[Employee Name] = CurrentEmployee &&
[Aux Name] = "break" &&
[Duration] >= 600), [Aux Start], , ASC, Dense) )
RETURN IF ( NOT ISEMPTY(FilteredTable), MINX(FILTER(SecondBreakRow, [Rank] = 1), [Aux Start]), BLANK() )
Here is my dataset in table:
Table
thanks