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!!!!!
Yay! So glad to hear you finally got it sorted. rdops I knew you could do it!
Go and take a much deserved break, and when you're ready to tackle the names, come back and let us know how you get on. Here's a start idea for you:
To reconcile differences in names between the two systems, how many are there? If it's just a few you can do a Replace Values in the query editor.
If you have many, I would recommend creating yourself a 'mapping' table of sorts to reconcile the name differences between the two systems:
NameA NameB William Scott Will Scott F Smith Fred Smith M Jane Mary Jane Make sure NameA column has no duplicates. Then merge this mapping table into SysA table as a Left Outer join using Name column from the System A table and NameA from the mapping table. Click the arrow on the Mapping column to expand and select the NameB column. Close and Apply. Then you just need to replace the NameB part in the DAX.
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.
https://sites.google.com/site/allisonkennedycv