Forum Discussion
Mooihoek
Helper II
Helper IIPower BI Remove Duplcates based on measure value
I want to be able to remove some duplicate account numbers based on whether the account has expired or not. I'm using the following measure to identify whether the acccount has expired in a table based in the account expiry date.
e.g. Account Expiry = DATEDIFF(TODAY(),MAX('Table1'[Account End Date]),DAY), this gives me a value in the Account Expiry Day column, I then want to remove
As you can see by the below table, Account 2 appears twice, but only one is valid, e.g it has 10 days remaining (the other expired 35 days ago) . How can I remove the duplicates accounts which have expired?
| Sales Account Name | Account Expiry (Days | Acount Expiry | Account ID |
| Account 1 | -50 | 1/02/2018 | 1234 |
| Account 2 | -35 | 1/05/2018 | 12345 |
| Account 3 | -5 | 1/07/2018 | 123456 |
| Account 2 | 10 | 1/08/2018 | 12345 |
| Account 5 | 15 | 1/09/2018 | 12345678 |
| Account 6 | 20 | 1/10/2018 | 123456789 |
8 Replies
v-yuta-msftCommunity Support
Hi Mooihoek,
You can create a measure using DAX as below:
Result = CALCULATE(MAX(Table1[Account Expiry(Days)]), ALLEXCEPT(Table1, Table1[Sales Account Name]))
Regards,
Jimmy Tao