Forum Discussion

Matas's avatar
Matas
Advocate II
4 years ago
Solved

Calculations using DAX

Hi guys,   I am having a table with 18k+ rows, but it is only 715 unique (DISTINCT) ID's, which means only 715 unique documents with different ID's. I am trying to get the "Extracted" values, which...
DAX
Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Matas 
    Please try

    Extracted =
VAR T1 =
    VALUES ( TableName[DOCID] )
VAR T2 =
    ADDCOLUMNS (
        T1,
        "@NumOfBlanks", SUMX ( CALCULATETABLE ( TableName ), IF ( TableName[Value] = BLANK (), 1 ) )
    )
VAR T3 =
    FILTER ( T2, [@NumOfBlanks] = BLANK () )
RETURN
    COUNTROWS ( T3 )
    Blanks =
VAR T1 =
    VALUES ( TableName[DOCID] )
VAR T2 =
    ADDCOLUMNS (
        T1,
        "@NumOfBlanks", SUMX ( CALCULATETABLE ( TableName ), IF ( TableName[Value] = BLANK (), 1 ) )
    )
VAR T3 =
    FILTER ( T2, [@NumOfBlanks] <> BLANK () )
RETURN
    COUNTROWS ( T3 )
tamerj1's avatar
tamerj1
Community Champion
4 years ago

Matas 
Yes 100% you can follow the same approch

Matas's avatar
Matas
Advocate II
4 years ago

tamerj1 so if I understand correctly, for Exctracted which logic behind should be to display the value if CALL_POINT = "EX02" and VALUE <> BLANK(), the formula should look like this, if you can confirm it:

 
OCR Validation Ratio Extracted =
var T1 = VALUES(Table[DOCID])
var T2 = ADDCOLUMNS( T1, "@NumOfEx02", SUMX(CALCULATETABLE(Table), IF (Table[CALL_POINT] = "EX02" && Table[VALUE] <> BLANK(), 1))
)
var T3 = FILTER(T2, [@NumOfEx02] <> BLANK())
RETURN
COUNTROWS(T3)
  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Matas 
    Please use

    OCR Validation Ratio Extracted =
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 )
    OCR Validation Ratio Validated =
VAR T1 =
    VALUES ( Table[DOCID] )
VAR T2 =
    ADDCOLUMNS (
        T1,
        "@NumOfBlanks",
            SUMX (
                FILTER ( CALCULATETABLE ( Table ), Table[CALL_POINT] = "VA02" ),
                IF ( Table[VALUE] = BLANK (), 1 )
            )
    )
VAR T3 =
    FILTER ( T2, [@NumOfBlanks] = BLANK () )
RETURN
    COUNTROWS ( T3 )