Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Matas
Advocate II
Advocate II

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

Matas_0-1656330868132.png

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.

 

Matas_1-1656330908200.png

In this example, the output should be 2. Since the value between EX02 and VA02 differs in DOCID 654476 and 654477.

 

Regards,

Matas

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

View solution in original post

12 REPLIES 12
tamerj1
Super User
Super User

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

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
Super User
Super User

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

2.png

1.png

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 )

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

Hi @Matas

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

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

@Matas 

Would you please share a screenshot of your visual?

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.

Matas_0-1656778882000.png

 

Blanks for Amount - 715 out of 715 Documents

Matas_1-1656778952815.png

 

Validated for Amount - 2815 out of 715 documents.

Matas_2-1656779000275.png

 

Regards,

Matas

 

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

Hi @tamerj1 ,

 

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

Matas_0-1656781244784.png

 

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

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

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.