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

    tamerj1 wow, that worked!

    Thank you so much! Could you please explain the logic behind? Since I will need to apply this logic to some other functions to get Validated fields, etc.

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      Matas 
      We are simply grouping by the values of the [DOCID] column and for each value we retrieve the number of blank values ( in [Value] column ). IF we have at least one Blank then the Document is counted as blank otherwise it will be counted as Extracted

  • Matas's avatar
    Matas
    Advocate II
    4 years ago

    tamerj1 just thinking if I can apply the same login here?

     

    basically I am calculating the Extracted if the Call_Point = "EX02" and Value column is not Blank.
    Validated is the same, Call_Point = "VA02" and Value column is not Blank.

    Do you think I can apply the same logic? Or I need a way different approach? If you may answer to this one too, would be great!

     

    Regards,

    Matas

    • 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)