Dax - comparing rows in one table based on multiple parameters.

[Sharkybu]

Hello.
I have a very complicated and large database that i have to do multiple comparison between rows of one table.
I currently have a report build using power query but it made the report even heavier and every time i run it, it takes 2 and a half hours to run. so i'm trying to buildit  in DAX but i'm a little lost. 
My table contains 11 columns, but five are important for my question

ID	Table	unit	Contract	Date	6 more columns
I need to compare the rows per ID	contains only two values (test/prod)	contains multiple values	contains multiple values	contains multiple values

Each ID can have multiple rows and I need to create comparisons based on three things:
1- rows where the tables are different and the unit and contract is the same.

2- rows where the table and contract is the same 
3- rows where the table and unit are the same and the date is the next one (in power query i added an index based on the date)
 
in power query i referenced the table three times and then merged it with itself based on the written above and then compared the rest of the columns.
Is there a way I can Make these comparison in DAX?

I hope this makes sense and thank you in advance.

Edited for further clarification:

adding an example

row number (not part of the table)	Id	Table	Unit	Contract	date	other column
1	1	test	bb	11	01/02/2006
2	1	test	bb	22	02/05/2006
3	1	prod	bb	11	01/02/2006
4	1	prod	bb	22	02/02/2002
5	1	prod	bb	33	06/05/2009
6	1	prod	aa	11	01/02/2006
7	1	prod	aa	22	02/05/2006

example for the comparisons :

1- rows where the tables are different and the unit and contract is the same.

         * rows 1 and 3 / 2 and 4 
         then i need to check if the date match  

2- rows where the table and contract is the same 

         * rows 3 and 6 / 5 and 7        

       then i need to check if the date match  

3- rows where the table and unit are the same and the date is the next one (the most complicated one)

         * rows 1 and 2 / 4 and 3 / 3 and 5  / 6 and 7     

       then i need to check if the contracts are in order

final results

row number (not part of the table)	Id	Table	Unit	Contract	date	other column	outcome comparison 1	outcome comparison 2	outcome comparison 3
1	1	test	bb	11	01/02/2006
2	1	test	bb	22	02/05/2006		different date
3	1	prod	bb	11	01/02/2006				contracts in wrong order (22/11/33)
4	1	prod	bb	22	02/02/2002		different date	different date	contracts in wrong order (22/11/33)
5	1	prod	bb	33	06/05/2009				contracts in wrong order (22/11/33)
6	1	prod	aa	11	01/02/2006
7	1	prod	aa	22	02/05/2006			different date

This is the most simple example I have.

[Sharkybu · Accepted solution]

So I found a solution.

I dont't know if its the best- but it works.

I created a bunch of new columns using the LOOKUPVALUE commend.

The columns I created per situation

example for the comparisons :

1- rows where the tables are different and the unit and contract is the same.
I brought the date from the rows with Table=prod in cases where the unit and contract match

DateIn2Table= LOOKUPVALUE('Table'[Date], 'Table'[Id], 'Table'[Id], 'Table'[Unit], 'Table'[unit], 'Table'[Contract], 'Table'[Contract], 'Table'[Table] = "prod")

and then I just added a column comparing dates between Table=test and  Table= prod

outcome comparison 1= IF('table'[Table]= "prod", "", 
                      IF('Table'[Date]='table'[DateIn2Table], "", "Different Date"
                       ))

2- rows where the table and contract is the same 

         I first created an index for the different units per Id and table.
         I needed to create a new column that combined the Id and table

IdTable = 'Table'[Id] & 'Table'[Table]

and then the index

Index2 = RANK(DENSE, ORDERBY('Table'[Unit],ASC),PARTITIONBY('Table'[IdTable]))

Then I got the dates in cases where the Id, Contract ant table match and the index is 1 higher

2date = LOOKUPVALUE('Table'[Date], 'Table'[Id], 'Table'[Id], 'Table'[Contract], 'Table'[Contract], 'Table'[Table], 'Table'[Table], 'Table'[Index2], 'Table'[Index2]-1) 

and then I compered the two dates

outcome comparison 2= IF('Table'[Date]='table'[2Date] , "",  "Different Date")

3- rows where the table and unit are the same and the date is the next one (the most complicated one)

    I created another index based on Id, unit, table and start date.
First i created a new Id combining Id, unit, table.

IdUnitTable = 'Table'[Id] & 'Table'[Unit] & 'Table'[Table]

then the index

Index = RANK(DENSE, ORDERBY('Table'[Date],ASC),PARTITIONBY('Table'[IdUnitTable]))

then I got the contract from each earlier date

Pre_Contract = LOOKUPVALUE('Table'[Contract], 'Table'[Id], 'Table'[Id], 'Table'[Unit], 'Table'[Unit], 'Table'[Table], 'Table'[Table], 'Table'[Index], 'Table'[Index]-1) 

and last I compered the two contracts

outcome comparison 3 = IF('Table'[Index]=1, "",
                       IF('Table'[Contract]-'Table'[Pre_Contract]=11, "", "Contracts in wrong order"
                                          ))

The explanation is kinda messy, but I hope it will help someone in the future.

[ThomasWeppler]

Hi @Sharkybu 
I use Table as a placeholder for the name of your table.
1.  Navigate to this palce and add a new column

2. 
MEASURE =
IF (
Table[Table] <> Table[Contract]
&& Table[Unit] = Table[Contract],
1,
IF ( Table[Table] = Table[Contract], 2, BLANK () )
)

This should give you a 1 in the first example and a 2 in the secound example, I am not sure I understand what you want in the third example can you say a bit more about it?

But I hope this can get you going.

[Sharkybu]

I see that I didnt make sense, so I edited the post.

[danextian]

Hi @Sharkybu 

DAX is more optimized at scaning a table than M but with a very large one, you can expect for still a slow performance. That aside, please provide a workable sample data and your expected result from that.

[Sharkybu]

I know it will still take time, i'm just hoping it will be a little less time.

And I see that I didnt make sense, so I edited the post.