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

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

    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