Forum Discussion

Matas's avatar
Matas
Advocate II
4 years ago
Solved

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 aut...
DAX
DAX Commands and Tips
Help Requested
  • tamerj1's avatar
    tamerj1
    4 years ago

    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 ) )
    )
)
tamerj1's avatar
tamerj1
Community Champion
4 years ago

Matas 

Would you please share a screenshot of your visual?

Matas's avatar
Matas
Advocate II
4 years ago

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's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      Matas
      Advocate II
      4 years ago

      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's avatar
        tamerj1
        Community Champion
        4 years ago

        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.