cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bvilten Regular Visitor
Regular Visitor

Need help with DAX formula

Hello All,

I have a table as follows ( the actual table is thousands of records, and UserIDs may appear many times with different contract end dates). There are only 3 options for the Action column.

 

UserID  ContractEndDate       Action                        IsExpired

8888      1/1/2019                  Onboard                    True

8888      1/1/2019                  Offboard                    True

8888      1/1/2019                  Contract Extension     True

 

I have found the latest contract end date and determined if it is true or not. What I need to do now is determine if the UserID with the expired contract end date (CED) has an action of Offboard in one of the CED records. If it does not then that UserID should be sent to a calculated table for reporting.

5 REPLIES 5
Highlighted
AClerk New Contributor
New Contributor

Re: Need help with DAX formula

Hi @bvilten 
Not sure I get you.

But, one option is to create a flag column and then filter according to it.

Column = if(Table1[Action] = "Offboard",1,0)

2019-04-12 12_18_52-papercut - Remote Desktop Connection.png

 

Then:
2019-04-12 12_20_02-papercut - Remote Desktop Connection.png

bvilten Regular Visitor
Regular Visitor

Re: Need help with DAX formula

Hello @AClerk ,

 

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.

bvilten Regular Visitor
Regular Visitor

Re: Need help with DAX formula

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.

AClerk New Contributor
New Contributor

Re: Need help with DAX formula

@bvilten 

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!

bvilten Regular Visitor
Regular Visitor

Re: Need help with DAX formula

@AClerk , 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    
UserIDLCEDACTIONEXPIREDAED UserIDLCEDACTIONEXPIRED
8888881/1/2019OnboardExpired      
8888892/2/2019OnboardExpired  8888892/2/2019VerifyExpired
8888903/3/2019CEExpired      
8888914/4/2019OnboardExpired  8888914/4/2019VerifyExpired
8888925/5/2019OnboardValid      
8888936/6/2019CEValid      
8888947/7/2019OnboardValid      
8888958/8/2019OnboardValid      
8888969/9/2019OnboardValid      
8888881/1/2019OffboardExpired1/1/2019     
8888892/2/2019CEExpired      
8888903/3/2019OffboardExpired3/3/2019     
8888914/4/2019CEExpired      

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 440 members 4,127 guests
Please welcome our newest community members: