Forum Discussion
DAX Comparison between tables
Hi,
I am having an issue and cannot figure it out, since I am still pretty new on DAX. Currently, I am having a table in the database, where it stores the "Extracted" documents that have been automatically extracted value by the software. I also have the second step, during this process, called Validation - "VA02", the value can be manually changed or leave it as it came from "EX02".
I need to create a DAX measure that would return the distinct count of Document ID for the count of CALL_POINT = "VA02" that Values differ from the CALL_POINT = "EX02".
The first row of EX02 corresponds with the first row of VA02, etc. There is a difference in lines 2 and 5, which means the value was changed manually. So the expected result, in this case, should be 1.
In this example, the output should be 2. Since the value between EX02 and VA02 differs in DOCID 654476 and 654477.
Regards,
Matas
Hi Matas
Pleas etry this codeDocument Count = SUMX ( SUMMARIZE ( 'Raw Data','Raw Data'[REGID], 'Raw Data'[DOCID] ), CALCULATE ( VAR EXTable = FILTER ( 'Raw Data', [CALL_POINT] = "EX02" ) VAR VATable = FILTER ( 'Raw Data', [CALL_POINT] = "VA02" ) VAR T1 = ADDCOLUMNS ( EXTable, "@Rank", RANKX ( EXTable, [Index],, ASC, Dense ) ) VAR T2 = ADDCOLUMNS ( T1, "@Value", VAR CurrentRank = [@Rank] VAR T3 = TOPN ( CurrentRank, VATable, [Index], ASC ) RETURN MAXX ( T3, [VALUE] ) ) VAR T4 = FILTER ( T2, [VALUE] <> [@Value] ) VAR T5 = SELECTCOLUMNS ( T4, "@DOCID", [DOCID] ) RETURN COUNTROWS ( DISTINCT ( T5 ) ) ) )
12 Replies
Hi Matas
Here is a sample file with the solution https://we.tl/t-36zvTh2N5ODoc. Count = VAR EXTable = FILTER ( Extracted, [CALL_POINT] = "EX02" ) VAR VATable = FILTER ( Extracted, [CALL_POINT] = "VA02" ) VAR T1 = ADDCOLUMNS ( EXTable, "@Rank", RANKX ( EXTable, [Index],, ASC, Dense ) ) VAR T2 = ADDCOLUMNS ( T1, "@Value", VAR CurrentRank = [@Rank] VAR T3 = TOPN ( CurrentRank, VATable, [Index], ASC ) RETURN MAXX ( T3, [VALUE] ) ) VAR T4 = FILTER ( T2, [VALUE] <> [@Value] ) RETURN COUNTROWS ( T4 )Hi Matas
Pleas etry this codeDocument Count = SUMX ( SUMMARIZE ( 'Raw Data','Raw Data'[REGID], 'Raw Data'[DOCID] ), CALCULATE ( VAR EXTable = FILTER ( 'Raw Data', [CALL_POINT] = "EX02" ) VAR VATable = FILTER ( 'Raw Data', [CALL_POINT] = "VA02" ) VAR T1 = ADDCOLUMNS ( EXTable, "@Rank", RANKX ( EXTable, [Index],, ASC, Dense ) ) VAR T2 = ADDCOLUMNS ( T1, "@Value", VAR CurrentRank = [@Rank] VAR T3 = TOPN ( CurrentRank, VATable, [Index], ASC ) RETURN MAXX ( T3, [VALUE] ) ) VAR T4 = FILTER ( T2, [VALUE] <> [@Value] ) VAR T5 = SELECTCOLUMNS ( T4, "@DOCID", [DOCID] ) RETURN COUNTROWS ( DISTINCT ( T5 ) ) ) )
