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 means values that are not blank or empty, and also to get the Blanks from this table based on the field and to visuals into a chart. So far I have:

 

My DAX measures are: 

CountBlanks:

 

Extracted:

 

My issue appears as it can be seen from the visual above that let's say for Amount field I am Extracting 4k and I have 1,6k Blanks (which returns everything from the table without having in mind that the Row should be DISTINCT on DocId).

How can I achieve the same result, but just return the result based on Distinct DOCID, that it would take the output based on 715 documents (Distinct) and not based on 18k Docs (not distinct)? So the end result should be maximum Extracted or Blank 715 or in between this number.

 

Thanks!

Matas

 

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 )

13 Replies

  • 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

  • Matas's avatar
    Matas
    Advocate II
    4 years ago

    tamerj1  Thank you a lot for your help. I was struggling with this for the last day. You just saved my day!

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Matas 

    so you are trying to calculate the number of document that have at least one blank. Also the the number of documents that have no blanks at all. Or the final goal is to calculate directly the documents that have no blanks at all?

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