Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
hi,
i have flight data as below
person column, status column, date column
person1, fly in, 1/2/21
person1, fly out, 11/2/21
person2, fly in 3/2/21
person2, fly out, 23/2/21
how do i create a new table or colum to expand the dates in between with the following rules:
Dates beteween fly in and fly out is filled in with status Work
Dates between fly out and fly in is filled with status Home.
Thanks in advance.
Regards,
HI @Anonymous,
You can try to use following calculate table formula to expand raw table and replace the status:
Expand =
VAR _calendar =
CALENDAR (
MINX ( ALL ( 'Sample'[record date] ), [record date] ),
MAXX ( ALL ( 'Sample'[record date] ), [record date] )
)
RETURN
SELECTCOLUMNS (
FILTER (
CROSSJOIN ( VALUES ( 'Sample'[person] ), _calendar ),
VAR currPerson = [person]
RETURN
[Date]
>= MINX ( FILTER ( ALL ( 'Sample' ), [person] = currPerson ), [record date] )
&& [Date]
<= MAXX ( FILTER ( ALL ( 'Sample' ), [person] = currPerson ), [record date] )
),
"Person", [person],
"Date", [Date],
"Status",
IF (
[Date]
IN SELECTCOLUMNS (
FILTER ( ALL ( 'Sample' ), [person] = EARLIER ( 'Sample'[person] ) ),
"Date", [record date]
),
"Home",
"Work"
)
)
Regards,
Xiaoxin Sheng
Thanks Xiaoxin @v-shex-msft
This works nicely as in the first 2 columns below (Dates, Query Status). However, the actual status is in the "Actual Status" column.
Back to original data i posted earlier, how do i change the DAX to make use of column with status "Fly in", Fly out" to get the Query status to match the Actual status plesae?
Thank you.
table for the above
Hi @Anonymous
Based on the sampele data you have provided, create a new table as below
_Work = SUMMARIZE(table,table[person],"fly in",CALCULATE(Min(table[date]),ALLEXCEPT(table,table[person])),"fly out",CALCULATE(Max(table[date]),ALLEXCEPT(table,table[person])))
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
98 | |
96 | |
68 | |
67 |