Forum Discussion

sunah132's avatar
sunah132
Helper I
2 years ago

Find mismatch count from two different table

Hello! I'm trying to find mismatch count on two tables. They are both direct query and one of the table is really huge where data size is about 300,000,000. But I only need this month data to minimize the size. Example would be like this and I tried to merge as new by filter this month but it kept on spinning that did not bringing data for any.

How can I build a DAX column to match table A to table B? Their terms are different as site ID and location ID but same value I need to compare.

It would be really appreciated! Thank you

 

Need Help

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    You may read up on the LOOKUPVALUE() function but i doubt it will work on such a large dataset.

  • sunah132's avatar
    sunah132
    Helper I
    2 years ago

    Thank you, lookup value will be used after create table of selected columns? If the data size is big, is it possible to bring columns with date filtered?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi sunah132 ,

     

    1. Since one of your tables is very large and you only need this month's data, you can apply a filter to the table to filter out only this month's data. Functions such as year and month can be used. For detailed information, please refer to the document: YEAR function (DAX) - DAX | Microsoft Learn.

     

    2. Create a calculated column to compare unmatched data.

    Match=
VAR flag =
CALCULATE(
COUNTROWS('Table B'),
FILTER(
'Table B',
'Table A'[Account] = 'Table B'[Account] && NOT 'Table A'[site ID] IN VALUES('Table B'[location ID])
)
)
RETURN
IF(flag = BLANK(),"match","not match")

    3. The final result is shown in the figure below.

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.