Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Dax Comparing Two Columns From Different Tables Returning Incorrect Result

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...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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] ) 
        )
    )
)
BrianConnelly's avatar
BrianConnelly
Icon for Resolver III rankResolver III
3 years ago

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's avatar
BrianConnelly
Icon for Resolver III rankResolver III
3 years ago

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