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.
I have a Dataset for an Item-"Tool" which is used more than one time , Everytime it is used there is an increamental "Sequence" recorded and the end "Date Out" with reason . It returns to Base for Service and next use the Sequence is Reset to '1"
The Objective is to add a column " Previous Reason" where it will copy the reason of the previous sequence use. If the "Sequence" is 1 then it will be blank .
Create a Measure called " Previous Reason"
For Every "Tool ",If the "Sequence" is >1 , Look for the same Tool #, current Sequence minus 1 ,earlier date , match found then copy the Value of the "Reason" of that Row to " Previous Reason"
I am not able to create measure with 2 Conditions ... Can you help please.
The Final Table in PBI should look like this with new Measure Column " Previous Reason"
Tool | Date Out | Sequence | Reason | Previous Reason |
T1 | 30-Oct-21 | 1 | Well Total Depth | |
T1 | 24-Nov-21 | 1 | Penetration Rate | |
T1 | 26-Nov-21 | 2 | Downhole Tool Failure | Penetration Rate |
T10 | 8-Oct-21 | 1 | Hole Problems | |
T10 | 12-Oct-21 | 2 | Well Total Depth | Hole Problems |
T10 | 22-Oct-21 | 1 | Downhole Tool Failure | |
T10 | 30-Oct-21 | 2 | Pump Pressure | Downhole Tool Failure |
T10 | 7-Nov-21 | 3 | Downhole Steerable Tool Failure | Pump Pressure |
T10 | 14-Nov-21 | 1 | Section Total Depth | |
T10 | 15-Nov-21 | 2 | Section Total Depth | Section Total Depth |
T10 | 16-Nov-21 | 3 | Section Total Depth | Section Total Depth |
T10 | 19-Nov-21 | 4 | Penetration Rate | Section Total Depth |
T10 | 20-Nov-21 | 5 | Section Total Depth | Penetration Rate |
T10 | 25-Nov-21 | 6 | Well Total Depth | Section Total Depth |
T10 | 2-Dec-21 | 7 | Well Total Depth | Well Total Depth |
T10 | 11-Dec-21 | 8 | Hours on Bit | Well Total Depth |
T11 | 18-Oct-21 | 1 | Section Total Depth | |
T11 | 19-Oct-21 | 2 | Section Total Depth | Section Total Depth |
T11 | 21-Oct-21 | 3 | Section Total Depth | Section Total Depth |
T11 | 8-Nov-21 | 4 | Well Total Depth | Section Total Depth |
Solved! Go to Solution.
Hi,
Please check the below DAX formula and the attached pbix file.
It is for creating a new column.
Previous reason CC =
VAR currenttool = Data[Tool]
VAR currentdate = Data[Date Out]
VAR previousdate =
MAXX (
FILTER ( Data, Data[Tool] = currenttool && Data[Date Out] < currentdate ),
Data[Date Out]
)
VAR currentsequence = Data[Sequence]
VAR previoussequence =
MAXX (
FILTER ( Data, Data[Tool] = currenttool && Data[Sequence] < currentsequence ),
Data[Sequence]
)
RETURN
MAXX (
FILTER (
Data,
Data[Tool] = currenttool
&& Data[Date Out] = previousdate
&& Data[Sequence] = previoussequence
),
Data[Reason]
)
Hi,
Please check the below DAX formula and the attached pbix file.
It is for creating a new column.
Previous reason CC =
VAR currenttool = Data[Tool]
VAR currentdate = Data[Date Out]
VAR previousdate =
MAXX (
FILTER ( Data, Data[Tool] = currenttool && Data[Date Out] < currentdate ),
Data[Date Out]
)
VAR currentsequence = Data[Sequence]
VAR previoussequence =
MAXX (
FILTER ( Data, Data[Tool] = currenttool && Data[Sequence] < currentsequence ),
Data[Sequence]
)
RETURN
MAXX (
FILTER (
Data,
Data[Tool] = currenttool
&& Data[Date Out] = previousdate
&& Data[Sequence] = previoussequence
),
Data[Reason]
)
Hello Jiwan,
Thanks for the Solution , It works , Fantastic ...Just that I have the data in seperate Tables , Like Date is n DIM Date , Tool is in DIM Tool and Rest in FACT table.
The Solution works when all the data is in one Table.
Is it possible to create a Measure instead of Column??
Hi,
Thank you for your message.
I do not know how your data model looks like, and please share your sample pbix file. And then I can try to create a more accurate measure that suits your needs.
Thank you.
Hello Jihwan
Please find the link from my Google Drive: kish.pbix
https://drive.google.com/file/d/1mGGl2YSFPrkif8liwJuZ9YSFeU9NlkeR/view?usp=sharing
If it does not work let me know.. Will try another option
I have included both version -
All data in single table- Which is working
Date is different table with relationship -
Thank You
User | Count |
---|---|
25 | |
11 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |