Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi everyone!
I'm new to Power BI!
I'm trying to create a "binary" column that should set the value 1 if two conditions are present simultaneously: the period is overlapped to others in the table (ex. row 1 and 6) and the production period in that row is longer.
As an example of my data.
Machinery | Date | Start_prod | End_prod | Minuts_prod | Check Overlap |
A | 01/02/2019 | 01/02/2019 08:00 | 01/02/2019 09:10 | 70 | 1 |
A | 01/02/2019 | 01/02/2019 09:00 | 01/02/2019 09:10 | 10 | 0 |
B | 01/02/2019 | 01/02/2019 08:00 | 01/02/2019 12:00 | 240 | 0 |
B | 01/02/2019 | 01/02/2019 13:00 | 01/02/2019 14:00 | 60 | 0 |
A | 01/02/2019 | 01/02/2019 12:00 | 01/02/2019 13:00 | 60 | 0 |
A | 02/02/2019 | 02/02/2019 08:00 | 02/02/2019 09:10 | 70 | 1 |
A | 02/02/2019 | 02/02/2019 08:30 | 02/02/2019 09:10 | 40 | 0 |
Can you help me please? Thanks in advance!
Solved! Go to Solution.
Autofind solution 🙂
Period Overlap =
VAR Overlapped =
COUNTROWS(
FILTER(
ALL(Table);
Table[Start_prod] <= EARLIER(Table[Start_prod]) &&
Table[End_prod] >= EARLIER(Table[End_prod]) &&
Table[Date] = EARLIER(Table[Date]) &&
Table[Machinery] = EARLIER(Table[Machinery]) &&
Table[Id] <> EARLIER(Table[Id])
)
)
RETURN
IF(Overlapped; "YES"; "NO")
Autofind solution 🙂
Period Overlap =
VAR Overlapped =
COUNTROWS(
FILTER(
ALL(Table);
Table[Start_prod] <= EARLIER(Table[Start_prod]) &&
Table[End_prod] >= EARLIER(Table[End_prod]) &&
Table[Date] = EARLIER(Table[Date]) &&
Table[Machinery] = EARLIER(Table[Machinery]) &&
Table[Id] <> EARLIER(Table[Id])
)
)
RETURN
IF(Overlapped; "YES"; "NO")
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |