Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I am having an issue and cannot figure it out, since I am still pretty new on DAX. Currently, I am having a table in the database, where it stores the "Extracted" documents that have been automatically extracted value by the software. I also have the second step, during this process, called Validation - "VA02", the value can be manually changed or leave it as it came from "EX02".
I need to create a DAX measure that would return the distinct count of Document ID for the count of CALL_POINT = "VA02" that Values differ from the CALL_POINT = "EX02".
The first row of EX02 corresponds with the first row of VA02, etc. There is a difference in lines 2 and 5, which means the value was changed manually. So the expected result, in this case, should be 1.
In this example, the output should be 2. Since the value between EX02 and VA02 differs in DOCID 654476 and 654477.
Regards,
Matas
Solved! Go to Solution.
Hi @Matas
Pleas etry this code
Document Count =
SUMX (
SUMMARIZE ( 'Raw Data','Raw Data'[REGID], 'Raw Data'[DOCID] ),
CALCULATE (
VAR EXTable =
FILTER ( 'Raw Data', [CALL_POINT] = "EX02" )
VAR VATable =
FILTER ( 'Raw Data', [CALL_POINT] = "VA02" )
VAR T1 =
ADDCOLUMNS ( EXTable, "@Rank", RANKX ( EXTable, [Index],, ASC, Dense ) )
VAR T2 =
ADDCOLUMNS (
T1,
"@Value",
VAR CurrentRank = [@Rank]
VAR T3 = TOPN ( CurrentRank, VATable, [Index], ASC )
RETURN
MAXX ( T3, [VALUE] )
)
VAR T4 =
FILTER ( T2, [VALUE] <> [@Value] )
VAR T5 = SELECTCOLUMNS ( T4, "@DOCID", [DOCID] )
RETURN
COUNTROWS ( DISTINCT ( T5 ) )
)
)
Hi @Matas
Pleas etry this code
Document Count =
SUMX (
SUMMARIZE ( 'Raw Data','Raw Data'[REGID], 'Raw Data'[DOCID] ),
CALCULATE (
VAR EXTable =
FILTER ( 'Raw Data', [CALL_POINT] = "EX02" )
VAR VATable =
FILTER ( 'Raw Data', [CALL_POINT] = "VA02" )
VAR T1 =
ADDCOLUMNS ( EXTable, "@Rank", RANKX ( EXTable, [Index],, ASC, Dense ) )
VAR T2 =
ADDCOLUMNS (
T1,
"@Value",
VAR CurrentRank = [@Rank]
VAR T3 = TOPN ( CurrentRank, VATable, [Index], ASC )
RETURN
MAXX ( T3, [VALUE] )
)
VAR T4 =
FILTER ( T2, [VALUE] <> [@Value] )
VAR T5 = SELECTCOLUMNS ( T4, "@DOCID", [DOCID] )
RETURN
COUNTROWS ( DISTINCT ( T5 ) )
)
)
Hi @tamerj1 ,
I am pretty sure this is what I needed. Thank you so much for this! I really appreciate your effort in helping me!
Regards,
Matas
Hi @Matas
Here is a sample file with the solution https://we.tl/t-36zvTh2N5O
Doc. Count =
VAR EXTable =
FILTER ( Extracted, [CALL_POINT] = "EX02" )
VAR VATable =
FILTER ( Extracted, [CALL_POINT] = "VA02" )
VAR T1 =
ADDCOLUMNS ( EXTable, "@Rank", RANKX ( EXTable, [Index],, ASC, Dense ) )
VAR T2 =
ADDCOLUMNS (
T1,
"@Value",
VAR CurrentRank = [@Rank]
VAR T3 = TOPN ( CurrentRank, VATable, [Index], ASC )
RETURN
MAXX ( T3, [VALUE] )
)
VAR T4 =
FILTER ( T2, [VALUE] <> [@Value] )
RETURN
COUNTROWS ( T4 )
Hi @tamerj1
Thank you for the reply, I really appreciate it. Could I ask how could I optimize this code to Distinct DOCID? Since I do not have an Index in my tables.
Regards,
Matas
Hi @Matas
index column must be added using power query. I don't believe this problem can be solved otherwise.
Hi @tamerj1 ,
Thank you for your reply. I have added the Index column, but the output returns not DISTINCT Docid result. How could I optimize your code, so that it would return only DISTINCT DOCID RowCount?
Regards,
Matas
Sure @tamerj1 .
My visual consists of 3 measures - Blanks, Extracted and Validated. This measure that we are trying to achieve is for Validation. Please check below the visual with the Doc Count, this is the issue. Thanks in advance!
Extracted for Amount - 695 out of 715 documents.
Blanks for Amount - 715 out of 715 Documents
Validated for Amount - 2815 out of 715 documents.
Regards,
Matas
I hope this will solve your problem
Doc. Count =
VAR EXTable =
FILTER ( Extracted, [CALL_POINT] = "EX02" )
VAR VATable =
FILTER ( Extracted, [CALL_POINT] = "VA02" )
VAR T1 =
ADDCOLUMNS ( EXTable, "@Rank", RANKX ( EXTable, [Index],, ASC, DENSE ) )
VAR T2 =
ADDCOLUMNS (
T1,
"@Value",
VAR CurrentRank = [@Rank]
VAR T3 =
TOPN ( CurrentRank, VATable, [Index], ASC )
RETURN
MAXX ( T3, [VALUE] )
)
VAR T4 =
FILTER ( T2, [VALUE] <> [@Value] )
VAR T5 =
SELECTCOLUMNS ( T4, "@ID", [DOCID] )
RETURN
COUNTROWS ( DISTINCT ( T4 ) )
Hi @tamerj1 ,
Thank you for your reply. Unfortunately I am getting exactly the same result by applying your new formula:
I am adding my formula for "Extracted" measure, maybe this will help out:
OCR Extracted Ratio = 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) |
If you have any idea of how to optimize your previous measure, I would be grateful.
Regards,
Matas
Is it me who wrote that measure? It ssems that O don't fully understand your requirement for the validation measure. I prefer to connect via teams or zoom perhaps on Monday. It is really difficult in some cases to auther codes without having your hands on the real data.
Hi @tamerj1 ,
Yes, it was you who helped me with this measure as well.
This sounds great, how could we connect via teams? Could you send me a private message?
And sorry that I cannot publish pbix file or show the full data behind it. This is data from the client, so I am not allowed to do so.
Regards,
Matas
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
42 | |
37 | |
22 | |
22 | |
21 |