Hello, I have a 2.5M row table of pay records. Each employee can have multiple pay records for each pay date. I would like to create a table that has a single record for each employee on each payday but includes all other columns in the table. I am currently doing this in Power Query by first creating a new column that concatenates employee ID and pay date (EID_PAY), then referencing the original table and removing duplicates values in the EID_PAY column, but I am sure there is a more efficient way to do it in DAX.
The way I do it now I end up bringing a lot of extra data into my model that I don’t really need, as it’s all contained in the original pay table and I just need a way to dynamically filter out duplicates in the EID_PAY column so I can do some math on other attributes (age at date of pay, for example).
Any help would be appreciated.