Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Matas
Advocate II
Advocate II

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:

Matas_1-1655886205097.png

Matas_0-1655886193181.png

 

My DAX measures are: 

CountBlanks:

Matas_2-1655886285274.png

 

Extracted:

Matas_3-1655886342934.pngMatas_4-1655886356128.png

 

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

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

View solution in original post

13 REPLIES 13
Matas
Advocate II
Advocate II

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

tamerj1
Super User
Super User

@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 just thinking if I can apply the same login here?

Matas_0-1655901579172.png

 

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 
Yes 100% you can follow the same approch

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

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

@tamerj1 wow, that worked!

Matas_0-1655900595406.png

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.

@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
Super User
Super User

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?

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.

Matas_0-1655895559549.png

 

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

 

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

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

Matas_0-1655897240132.png

 

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

Matas_1-1655897348321.png

 

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

Matas_0-1655896696867.png

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors