Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Attached excel file contains the data set.
What I am trying to accomplish in Power BI is
There is an Entered Data and a Served Date and air craft number
What I need is a column to say that this group 1 , then group 2 , group 3 etc....
check if f2 is greater than e2 then start with e2 and f2 is maximum |
if e3 less than f2 then e2 Is minimum value and f3 is maximum |
if e4 less than f3 then e2 is minimum and f4 is greater than f3 , if f4 is less than f3 then f3 still remains maximum |
if e5 is greater than the minimum value then e2 is minimum, check if f5 greater than maximum , if not then f3 is maximum |
I am trying to attach a file but I can't
so I am entering excel , i am trying to get EVENT column using Dax formula or M code whichever is easy
Index | Aircraft | DE id | Event | Start | Stop | Duration (days) | Event | minimum | maximum | |||
1 | 101 | DE_101_01 | Record_3 | 1-Jan-22 | 9-Feb-22 | 39 | Event 1 - 101 | e2 | f2 | check if f2 is greater than e2 then start with e2 and f2 is maximum | ||
2 | 101 | DE_101_01 | Record_1 | 3-Jan-22 | 17-Feb-22 | 45 | Event 1 - 101 | e2 | f3 | if e3 less than f2 then e2 Is minimum value and f3 is maximum | ||
3 | 101 | DE_101_01 | Record_2 | 9-Jan-22 | 7-Feb-22 | 29 | Event 1 - 101 | e2 | f3 | if e4 less than f3 then e2 is minimum and f4 is greater than f3 , if f4 is less than f3 then f3 still remains maximum | ||
4 | 101 | DE_101_01 | Record_4 | 5-Feb-22 | 12-Feb-22 | 7 | Event 1 - 101 | e2 | f3 | if e5 is greater than the minimum value then e2 is minimum, check if f5 greater than maximum , if not then f3 is maximum | ||
5 | 101 | DE_101_02 | Record_5 | 22-Feb-22 | 3-Mar-22 | 9 | Event 2 - 101 | e6 | f6 | |||
6 | 101 | DE_101_02 | Record_7 | 27-Feb-22 | 1-Mar-22 | 2 | Event 2 - 101 | e6 | f6 | |||
7 | 101 | DE_101_02 | Record_6 | 1-Mar-22 | 8-Mar-22 | 7 | Event 2 - 101 | e6 | f8 | |||
8 | 102 | DE_102_01 | Record_8 | 3-Feb-22 | 7-Feb-22 | Event 1 - 102 | ||||||
9 | 102 | DE_102_01 | Record_9 | 4-Feb-22 | 14-Feb-22 | Event 1 - 102 | ||||||
10 | 102 | DE_102_01 | Record_10 | 9-Feb-22 | 25-Mar-22 | Event 1 - 102 |
Basically , I am trying to capture a downing event. The downing event is when an aircraft is out of service. There are two dates, [Entered Date] and [Servicable Date]
Below are 3 examples of a downing event
Index | Aircraft | Entered Date | Servicable Date | Event | minimum | maximum | |
1 | 101 | 1-Jan-22 | 9-Feb-22 | Event 1 - 101 | e2 | f2 | |
2 | 101 | 3-Jan-22 | 17-Feb-22 | Event 1 - 101 | e2 | f3 | |
3 | 101 | 9-Jan-22 | 7-Feb-22 | Event 1 - 101 | e2 | f3 | |
4 | 101 | 5-Feb-22 | 12-Feb-22 | Event 1 - 101 | e2 | f3 | |
5 | 101 | 22-Feb-22 | 3-Mar-22 | Event 2 - 101 | e6 | f6 | |
6 | 101 | 27-Feb-22 | 1-Mar-22 | Event 2 - 101 | e6 | f6 | |
7 | 101 | 1-Mar-22 | 8-Mar-22 | Event 2 - 101 | e6 | f8 | |
8 | 102 | 3-Feb-22 | 7-Feb-22 | Event 1 - 102 | |||
9 | 102 | 4-Feb-22 | 14-Feb-22 | Event 1 - 102 | |||
10 | 102 | 9-Feb-22 | 25-Mar-22 | Event 1 - 102 |
The minimum and maximum values are the ranges for each downing event which I have put for logic purposes
For the first record (Index 1)
the assumption is the minimum value is row 2 as mentioned e2 (Entered date) and the maximum value is f2 (Sevicable date) this is the start of the downing event.
For the second record (Index 2) row 3
(e3) will check if e3 is less than f2 (servicable date) if yes then this record is part of the same downing event and if f2 < f3 then f3 is now the new servicable date instead of f2 for this downing event. So the current range of the downing event went from (1 Jan - 9 Feb) => (Jan 1 - 17 Feb) .
For the 3rd record (Index 3) 4th row
e4 will check if its less than f3, if yes then this record is part of the same downing event and if f4 (Feb 7 )< f3 ( Feb 17) , current range for the 1st downing event is [e2....f3] [Jan 1 - Feb 17]
For the 4th (Index 4) record, 5th row
e5 will check if its less than f3(maximum range point) , if yes then this record is part of the same downing event and if f5 (Feb 9 )< f3 ( Feb 17) , current range for the 1st downing event is [e2....f3] [Jan 1 - Feb 17]
For the 5th (Index 5) record, 6th row
e6 will check if its less than f3(maximum range point) , if yes then this record is part of the same downing event else new downing event starts as e6 is greater than f3
So in the event column it will be Event 2 +101 (Aircraft number) and the new range for this downing event will start from [e6 ..f6] (Feb 22 ...... Mar9)
Hi @FahQ2022 ,
According to your statement, I think your maximum/minimum is calculated based on the previous data. So you need to use looping in your calculation. As far as I know, Dax doesn't support looping. Here I suggest you to try M code to create a custom column in Power Query Editor.
For reference:
List.Generate() and Looping in PowerQuery
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Rico
I am not familiar with M , if someone can write the M code it would be greatly appreciated
Thanks
Hi @FahQ2022
can't follow your description here:
What I need is a column to say that this group 1 , then group 2 , group 3 etc....
check if f2 is greater than e2 then start with e2 and f2 is maximum
if e3 less than f2 then e2 Is minimum value and f3 is maximum
if e4 less than f3 then e2 is minimum and f4 is greater than f3 , if f4 is less than f3 then f3 still remains maximum
if e5 is greater than the minimum value then e2 is minimum, check if f5 greater than maximum , if not then f3 is maximum
can you rephrase it somehow?
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 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |