Calculations using DAX

[Matas]

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

[Matas]

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

[tamerj1 · Accepted solution]

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

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

@Matas 
Yes 100% you can follow the same approch

[Matas]

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

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

[Matas]

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

@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

[tamerj1]

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]

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]

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

[Matas]

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

[Matas]

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