Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |