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