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.

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

8 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    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] ) 
        )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Anonymous 

    please try

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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. 

  • BrianConnelly's avatar
    BrianConnelly
    Resolver 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.  

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    how the 1441 comes?

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