topic Re: Return Earlier or previous Date row data based two conditions - sequence and Tool number in DAX Commands and Tips

Return Earlier or previous Date row data based two conditions - sequence and Tool number

Anonymous — Sat, 30 Apr 2022 09:52:03 GMT

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

Re: Return Earlier or previous Date row data based two conditions - sequence and Tool number

Jihwan_Kim — Sat, 30 Apr 2022 10:13:48 GMT

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] )

Re: Return Earlier or previous Date row data based two conditions - sequence and Tool number

Anonymous — Sat, 30 Apr 2022 11:41:10 GMT

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??

Re: Return Earlier or previous Date row data based two conditions - sequence and Tool number

Jihwan_Kim — Sat, 30 Apr 2022 13:19:14 GMT

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.

Re: Return Earlier or previous Date row data based two conditions - sequence and Tool number

Anonymous — Sun, 01 May 2022 06:05:31 GMT

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