topic Re: Dax Comparing Two Columns From Different Tables Returning Incorrect Result in DAX Commands and Tips

Dax Comparing Two Columns From Different Tables Returning Incorrect Result

Anonymous — Wed, 15 Feb 2023 02:52:48 GMT

Hi, I have this dax function that is returning incorrect result for Feb 2023 when counting the number of barcodes that are not present in table 2 when comparing to table 1. Appreciate any help please.

Count of Missing Barcodes = COUNTROWS(FILTER('Table 1', NOT('Table 1'[Barcode] IN VALUES('Table 2'[Barcode]))))

Pbix file here⬇️
https://drive.google.com/file/d/1fEw5ywUZL4PBGc9k7GzmLL8iGPcAoL1U/view?usp=share_link

Re: Dax Comparing Two Columns From Different Tables Returning Incorrect Result

tamerj1 — Wed, 15 Feb 2023 03:04:38 GMT

Hi Anonymous

please try

Count of Missing Barcodes =
COUNTROWS (
EXCEPT ( VALUES ( 'Table 1'[Barcode] ), VALUES ( 'Table 2'[Barcode] ) )
)

Re: Dax Comparing Two Columns From Different Tables Returning Incorrect Result

Anonymous — Wed, 15 Feb 2023 03:16:46 GMT

tamerj1 thank you for the quick response but unfortunately that function you provided returned the same result. I attached the pbix file in my initial post if that helps.

Re: Dax Comparing Two Columns From Different Tables Returning Incorrect Result

BrianConnelly — Wed, 15 Feb 2023 03:21:01 GMT

Im not sure if your number is right. I first wanted to test if the barcode existed in the other table, so I created a calculated column in the 'DistProfile v1-3'. This was done right after I created a inactive relationship between

'DistProfile v1-3' and 'DASHBOARD_RESULTS_HIST (2)' based on the barcodes.

My calculated column has this formula:

Found In History = CALCULATE(MAX('DASHBOARD_RESULTS_HIST (2)'[BARCODE]),USERELATIONSHIP('DistProfile v1-3'[Barcode],'DASHBOARD_RESULTS_HIST (2)'[BARCODE]))

This resulted in the following:

Then I created a measure to count the barcodes that are not blank:

No Barcode Match = SUMX('DistProfile v1-3',IF(NOT(ISBLANK('DistProfile v1-3'[Found In History])),1,0))

This resulted....

which is 1298 and is lower than the two.

Re: Dax Comparing Two Columns From Different Tables Returning Incorrect Result

wdx223_Daniel — Wed, 15 Feb 2023 03:21:10 GMT

how the 1441 comes?

filter by date of 2023 Feb, the different count is 1463

Re: Dax Comparing Two Columns From Different Tables Returning Incorrect Result

BrianConnelly — Wed, 15 Feb 2023 03:26:00 GMT

If you remove the NOT from the formula, this counts the ones that are blank which is 1471. Also, rewrote based on the calculated column as

No Barcode Match = COUNTAX(FILTER('DistProfile v1-3',(ISBLANK('DistProfile v1-3'[Barcode Found In History])=TRUE())),'DistProfile v1-3'[Barcode])

with same 1471

Re: Dax Comparing Two Columns From Different Tables Returning Incorrect Result

Anonymous — Wed, 15 Feb 2023 03:45:14 GMT

BrianConnelly wdx223_Daniel thank you for your feedback. But after doing a quick analysis in Excel of the same dataset filtered for 2023 only, I came up with 1441 for Feb and 193 Jan which total 1634. All the numbers match in Excel vs Power Bi except for Feb. Am I missing something?

Excel file fyi⬇️
https://docs.google.com/spreadsheets/d/1duPWwIRiwhof_CK-7X-avZKztMaA_Vjb/edit?usp=share_link&ouid=106267973063939566544&rtpof=true&sd=true

Re: Dax Comparing Two Columns From Different Tables Returning Incorrect Result

tamerj1 — Wed, 15 Feb 2023 04:08:14 GMT

Anonymous
Is this what you're looking for?

Count of Missing Barcodes = COUNTROWS ( EXCEPT ( VALUES ( 'DistProfile v1-3'[Barcode] ), CALCULATETABLE ( VALUES ( 'DASHBOARD_RESULTS_HIST (2)'[BARCODE] ), ALLEXCEPT ( AcceptanceCalendar, AcceptanceCalendar[Year] ) ) ) )

Re: Dax Comparing Two Columns From Different Tables Returning Incorrect Result

Anonymous — Wed, 15 Feb 2023 04:13:06 GMT

tamerj1 genius! Thank you so much. That's correct and matches my Excel analysis. Appreciate your help with this.