Forum Discussion
Help comparing dates between tables
rdops try this DAX as a new TABLE from the modeling tab in the report:
Hi Allison, I did this but it shows exactly the same thing that is on Source A. I mean, this table displays exactly the same as if I had a table for Sorce A (without any DAX).
and I am officially done with this. Just cant make it work. Nothing that I do works. I cannot even look at this report anymore.
Also for the records, the number of columns and fields are different on each table so is not as straight forward. Anyway, I am done.
Thanks everyone for all your help.
Here's one more photo showing the sample with the other columns for reference:
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
rdops What the below DAX function does is check each of the columns listed in System A for a matching value in System B. If the employee name or leave type are spelled differently, then it won't find a match in System B and will return that value. If you're getting the same values in the DAX as in System A, then that means it has found 0 matching rows between the two tables. This could be because your column order is different, or slight spelling, or even something as small as one System has a space after the name while the other system doesn't.
Missing Hours =
EXCEPT (
SUMMARIZE (
'System A',
'System A'[Name],
'System A'[Start date],
'System A'[Leave type],
'System A'[Hours]
),
SUMMARIZE (
'System B',
'System B'[Name],
'System B'[Start date],
'System B'[Leave type],
'System B'[Hours]
)
)Here's what I get when I use the DAX:
Note if you want to filter the Missing Hours table by staff member, you'll need either a separate staff table with each staff member listed exactly once (preferred solution) or need to use the Name from the MissingHours summarized table.
I still think you might be better off doing this inside the Power Query Editor, which would require you to create a unique key column for each system that can be used to find a match or not. Try this link to see if it helps explain a bit about how you can do that: https://radacad.com/choose-the-right-merge-join-type-in-power-bi
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
OMG Allison!!!! It worked!!! It finally worked!!
The only thing I need to figure out now is how to resolve the names discrepancies between the two systems (eg, we have one name William Scott on system A and Will Scott on System B, in this case obvs William wont appear. Other than that, is f....king working.
I cannot thank you enough!!!!!
