Forum Discussion
Dax - comparing rows in one table based on multiple parameters.
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.
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 situationexample 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 matchDateIn2Table= 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 tableIdTable = '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.
5 Replies
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.
Hi Sharkybu
I use Table as a placeholder for the name of your table.
1. Navigate to this palce and add a new column2.
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.I see that I didnt make sense, so I edited the post.
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 situationexample 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 matchDateIn2Table= 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 tableIdTable = '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.
