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

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
Icon for Advocate II rankAdvocate 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