Dax Comparing Two Columns From Different Tables Returning Incorrect Result

[Anonymous]

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

[tamerj1 · Accepted solution]

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

[Anonymous]

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

[wdx223_Daniel]

how the 1441 comes?

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

[BrianConnelly]

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.  

[BrianConnelly]

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 

[Anonymous]

@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=10...

[tamerj1]

Hi @Anonymous 

please try

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

[Anonymous]

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