topic Re: DAX Comparison between tables in DAX Commands and Tips

DAX Comparison between tables

Matas — Mon, 27 Jun 2022 11:56:01 GMT

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

Re: DAX Comparison between tables

tamerj1 — Mon, 27 Jun 2022 16:42:05 GMT

Hi Matas
Here is a sample file with the solution https://we.tl/t-36zvTh2N5O

Doc. 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 )

Re: DAX Comparison between tables

Matas — Mon, 27 Jun 2022 22:18:21 GMT

Hi tamerj1

Thank you for the reply, I really appreciate it. Could I ask how could I optimize this code to Distinct DOCID? Since I do not have an Index in my tables.

Regards,

Matas

Re: DAX Comparison between tables

tamerj1 — Tue, 28 Jun 2022 02:17:47 GMT

Hi Matas

index column must be added using power query. I don't believe this problem can be solved otherwise.

Re: DAX Comparison between tables

Matas — Sat, 02 Jul 2022 15:06:09 GMT

Hi tamerj1 ,

Thank you for your reply. I have added the Index column, but the output returns not DISTINCT Docid result. How could I optimize your code, so that it would return only DISTINCT DOCID RowCount?

Regards,

Matas

Re: DAX Comparison between tables

tamerj1 — Sat, 02 Jul 2022 15:49:18 GMT

Matas

Would you please share a screenshot of your visual?

Re: DAX Comparison between tables

Matas — Sat, 02 Jul 2022 16:23:43 GMT

Sure tamerj1 .

My visual consists of 3 measures - Blanks, Extracted and Validated. This measure that we are trying to achieve is for Validation. Please check below the visual with the Doc Count, this is the issue. Thanks in advance!

Extracted for Amount - 695 out of 715 documents.

Blanks for Amount - 715 out of 715 Documents

Validated for Amount - 2815 out of 715 documents.

Regards,

Matas

Re: DAX Comparison between tables

tamerj1 — Sat, 02 Jul 2022 16:53:14 GMT

Matas

I hope this will solve your problem

Doc. 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] ) VAR T5 = SELECTCOLUMNS ( T4, "@ID", [DOCID] ) RETURN COUNTROWS ( DISTINCT ( T4 ) )

Re: DAX Comparison between tables

Matas — Sat, 02 Jul 2022 17:05:14 GMT

Hi tamerj1 ,

Thank you for your reply. Unfortunately I am getting exactly the same result by applying your new formula:

I am adding my formula for "Extracted" measure, maybe this will help out:

OCR Extracted Ratio =

var T1 = VALUES(Table[DOCID])

var T2 = ADDCOLUMNS(T1, "@NumOfBlanks", SUMX(FILTER(CALCULATETABLE(Table), Table[CALL_POINT] = "EX02"),

IF (Table[VALUE] <> BLANK(),1)

)

var T3 = FILTER(T2, [@NumOfBlanks] <> BLANK())

RETURN

COUNTROWS(T3)

If you have any idea of how to optimize your previous measure, I would be grateful.

Regards,

Matas

Re: DAX Comparison between tables

tamerj1 — Sat, 02 Jul 2022 17:16:11 GMT

Matas

Is it me who wrote that measure? It ssems that O don't fully understand your requirement for the validation measure. I prefer to connect via teams or zoom perhaps on Monday. It is really difficult in some cases to auther codes without having your hands on the real data.

Re: DAX Comparison between tables

Matas — Sat, 02 Jul 2022 17:23:21 GMT

Hi tamerj1 ,

Yes, it was you who helped me with this measure as well.

This sounds great, how could we connect via teams? Could you send me a private message?

And sorry that I cannot publish pbix file or show the full data behind it. This is data from the client, so I am not allowed to do so.

Regards,

Matas

Re: DAX Comparison between tables

tamerj1 — Tue, 05 Jul 2022 14:25:27 GMT

Hi Matas
Pleas etry this code

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

Re: DAX Comparison between tables

Matas — Tue, 05 Jul 2022 16:01:38 GMT

Hi tamerj1 ,

I am pretty sure this is what I needed. Thank you so much for this! I really appreciate your effort in helping me!

Regards,

Matas