Forum Discussion
Need help with DAX formula
Hello Anonymous ,
Thank you for responding. I'm not surprised you aren't getting me. I'm not sure my self ;-). Let me try again. What I am trying to get at is this.
Find all records for each d#, find each of last contract end date, search for "offboard" action within records containing last contract end date. If not found and last contract end date is expired then report record.
I have come up with the following formula:
ToBeChecked = IF(AND(OnOffboardingManagement[IsCEDExpired] = TRUE, ISBLANK(OnOffboardingManagement[ActualEndDate])),"Check","Varified")
Which works to a point. The problem is that I have both a record that fits the check scenerio, and a record that fits the Varified scenerio. If I get a hit on Varified then all other records for that UserID should be skipped.
Sorry. still not getting you, and I am guessing that others as well.
I suggest you will give an example of your original data set and the expected results, after manipulation of DAX script or other functionalities.
Cheers!
Anonymous , One mor try then :-)
In the Before table you will find that only the UserIDa 888889 and 888891 meet the "need to verify" criteria which is:
If a LCED is expired and there is no Offboard action for any of the records matching the LCED it must be varified. There has since been added an AED column which could be used as well. We match on the UserID and the LCED then look for either an AED or an Offboard action in the returned group.
BEFORE Processing AFTER Processing last contract end date actual end date UserID LCED ACTION EXPIRED AED UserID LCED ACTION EXPIRED 888888 1/1/2019 Onboard Expired 888889 2/2/2019 Onboard Expired 888889 2/2/2019 Verify Expired 888890 3/3/2019 CE Expired 888891 4/4/2019 Onboard Expired 888891 4/4/2019 Verify Expired 888892 5/5/2019 Onboard Valid 888893 6/6/2019 CE Valid 888894 7/7/2019 Onboard Valid 888895 8/8/2019 Onboard Valid 888896 9/9/2019 Onboard Valid 888888 1/1/2019 Offboard Expired 1/1/2019 888889 2/2/2019 CE Expired 888890 3/3/2019 Offboard Expired 3/3/2019 888891 4/4/2019 CE Expired