cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

1 ACCEPTED SOLUTION
Super User

@Matas

``````Extracted =
VAR T1 =
VALUES ( TableName[DOCID] )
VAR T2 =
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 =
T1,
"@NumOfBlanks", SUMX ( CALCULATETABLE ( TableName ), IF ( TableName[Value] = BLANK (), 1 ) )
)
VAR T3 =
FILTER ( T2, [@NumOfBlanks] <> BLANK () )
RETURN
COUNTROWS ( T3 )``````
13 REPLIES 13

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

Super User

@Matas

``````Extracted =
VAR T1 =
VALUES ( TableName[DOCID] )
VAR T2 =
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 =
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?

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

Super User

@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())RETURNCOUNTROWS(T3)
Super User

@Matas

``````OCR Validation Ratio Extracted =
VAR T1 =
VALUES ( Table[DOCID] )
VAR T2 =
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 =
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!

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.

Super User

@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

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.

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

Super User

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

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:

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors