DAX Comparison between tables

[Matas]

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

[tamerj1 · Accepted solution]

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

[Matas]

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

[tamerj1]

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 )

[Matas]

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

[tamerj1]

Hi @Matas

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

[Matas]

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

[tamerj1]

@Matas 

Would you please share a screenshot of your visual?

[Matas]

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

[tamerj1]

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

[Matas]

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

[tamerj1]

@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. 

[Matas]

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