Forum Discussion

Matas's avatar
Matas
Icon for Advocate II rankAdvocate 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
Icon for Community Champion rankCommunity Champion
4 years ago

Hi Matas

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

Matas's avatar
Matas
Icon for Advocate II rankAdvocate II
4 years ago

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

    Matas 

    Would you please share a screenshot of your visual?

    • Matas's avatar
      Matas
      Icon for Advocate II rankAdvocate 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
        Icon for Community Champion rankCommunity 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 ) )