Forum Discussion
DAX Comparison between tables
- 4 years ago
Hi Matas
Pleas etry this codeDocument 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
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.
- Matas4 years agoAdvocate II
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
- Matas4 years agoAdvocate II
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