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

Hi tamerj1 

 

Thank you for reply. My end goal is to show the Extracted and Blank values based on number of Distinct ID's categorized into separate fields.

 

Extracted means that my Column "VALUE" has any value, as long it is not null or Blank.

Blank, well it literally means Blank inside of "VALUE" column.

 

The solution should be that somehow I should calculate the Extracted and Blanks based on Distinct ID. Since my total number of distinct ID's is 715 documents, this graph maximum point should be 715. But depending on the Extracted and Blanks, it can be let's say 658 Extracted and 57 Blanks or so.

 

Hopefully I have not confused you even more.

 

Regards,

Matas

 

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Matas 
I think we're saying the same thing but in different languages. What are you slicing by in you chart?

  • Matas's avatar
    Matas
    Advocate II
    4 years ago

    tamerj1  I bilieve so too. I am slicing by the values from "Name" column and applying my calculations for Extracted and Blanks.

     

  • Matas's avatar
    Matas
    Advocate II
    4 years ago

    tamerj1 

    Just to clarify, I think my issue is that calculations are being made by taking whole data from the table and not Distinct values, since if I am applying just Count on my Document ID's, this is the result, since I have multiple rows with same ID, since other columns contains different values:

     

    The calculations should be made according to the Distinct ID's, which is: