Multiple Conditions into 1 Calculated Column

[Hero11]

Need some help to finish some DAX.  I have 2 Tables (A & B).  On Table-A, I have various columns one of which is a Column (TurnArounds) that lists turnaround times by day type: Expedited, Calendar Days, Working Days.  Table-B, contains 3 separate calculated columns corresponding to each turnaround type.  Both tables are linked by a primary identifier.

Table_A Example:

Unique_ID	TurnArounds	Grab DueDate (new col)
123	Expedited
456	Calendar Days
789	Working Days
012	Expedited

I want to return the values (calculated date values) from Table-B to Table-A in a new single column, based on a match of the ID & TurnArounds Column listed on each row in Table-A.  One date for each row in the new column on Table_A.

Table_B Example:

Unique_ID	Due_Date_Types	Expedited	Calendar Days	Working Days
012	Expedited	1/1/2025	2/1/2025	3/1/2025
789	Working Days	1/5/2025	2/5/2025	3/13/2025
123	Expedited	1/10/2025	2/15/2025	3/31/2025
456	Calendar Days	1/20/2025	2/24/2025	4/1/2025

 I started writing some DAX, that works for only one TurnAround scenario at a time, but not all 3. Can i please get some help to fill in Table_A?  Hopefully I was sort of on the right path as I'm still relatively new to PowerBI.  Thanks in advance.

Grab DueDate =

VAR vTrackNumber = 'Table_A'[UniqueID]

VAR vTable = FILTER('Table_B', 'Table_B'[UniqueID] = vTrackNumber

                      && 'Table_B'[Due_Date_Types] = {"Business Days", "Calendar Days", "Expedited"})

RETURN

                    CALCULATE(SELECTEDVALUE('Table_B'[Working_Days_Column]))

[divyed]

Hello @Hero11 ,

Please use below dax which will give you corresponding due_Dates. It will give you dates if available and leave blank if there is no matching values in table2 so you will have all rows from table1 and matching values from table2 . 

Grab DueDate =

VAR CurrentID = 'Tab_1'[Unique_ID]

VAR CurrentTurnAround = 'Tab_1'[TurnAround]

VAR ExpeditedDate = LOOKUPVALUE('Tab_2'[Expedited], 'Tab_2'[Unique_ID], CurrentID)

VAR CalendarDate = LOOKUPVALUE('Tab_2'[Calendar Days], 'Tab_2'[Unique_ID], CurrentID)

VAR WorkingDaysDate = LOOKUPVALUE('Tab_2'[Working Days], 'Tab_2'[Unique_ID], CurrentID)

RETURN

    SWITCH(

        TRUE(),

        CurrentTurnAround = "Expedited" && NOT ISBLANK(ExpeditedDate), ExpeditedDate,

        CurrentTurnAround = "Calendar Days" && NOT ISBLANK(CalendarDate), CalendarDate,

        CurrentTurnAround = "Working Days" && NOT ISBLANK(WorkingDaysDate), WorkingDaysDate,

        BLANK()  -- If no match or date not found

    )

I hope this will work, if not kindly share expected output to check further.

Did I answer your query ? Mark this as solution if this helps , Kudos are appreciated.

Warm Regards,

Neeraj

[Hero11]

Hi @shafiz_p ,

Thank you first of all for your prompt response.  Okay, I plugged in your code into a new Calculated Column on Table_A and it kind or worked.  It returned a date value for my Expedited days as expected, but returned Blanks for both Calendar & Business Days.  Reading the code it looks like it logically validates the Unique_ID & TurnArounds day types.  Looks so promising.  Any suggestions? 

Additional info: Table_B is comprised of the same date data for all 3 types of TurnArounds: Calendar, Business, Calendar.  The 2 Tables are linked with a 1:1 Relationship.  When I substitue the RETURN part of my sample code to be specific to Calendar or Business or Expedited it does provide a date on Table_A.  It doesn't work for all 3 simultaneously. Was trying to test the values in Table_B, but it seems it returns the values in some instances when I use my old code. For example:

     ... 

     RETURN

     CALCULATE(SELECTEDVALUE('Table_B'[Working Days]))

--top works for working days only & same thing if I call out Calendar Days specifically

I'll try to keep hammering away at it.  Let me know if you have any alternate suggestions/thoughts/ideas.  Greatly appreciate any feedback.

[Anonymous]

Hi ALL,
Firstly  shafiz_p  thank you for your solution!
And @Hero11 ,Based on your current problem, you may not be able to return the values of the other date columns correctly right?
Since your primary keys are concatenated on a one-to-one or one-to-many basis, then we recommend that you use the realted function to get the values that you need to accomplish what you need to do.

Grab DueDate = 
SWITCH(
    Table_A[TurnArounds],
    "Expedited", RELATED(Table_B[Expedited]),
    "Calendar Days", RELATED(Table_B[Calendar Days]),
    "Working Days", RELATED(Table_B[Working Days])
)

I hope my code is helpful and I would be honored if it solves your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Hero11]

Hey @Anonymous ,

Looks like the same outcome.  It returns the type Expedited, but blanks for both Calendar Days or Business Days.  You're correct, the Tables have a One-to-One relationship connected by the Unique_ID (primary key).  Thank you for the input.  Can I provide any additional info that would help figure out the issue?

[Anonymous]

Hi @Hero11 ,
Can you provide some example data or some pbix files so that we can better help you to solve the problem, you can upload your pbix files in the form of web storage so that we can better help you to solve the problem!

[shafiz_p · Accepted solution]

Hi @Hero11  Try the below code:

Grab DueDate = 
VAR CurrentID = 'Table A'[Unique_ID]
VAR CurrentTurnAround = 'Table A'[TurnArounds]
RETURN
SWITCH(
    CurrentTurnAround,
    "Expedited", LOOKUPVALUE('Table B'[Expedited], 'Table B'[Unique_ID], CurrentID),
    "Calendar Days", LOOKUPVALUE('Table B'[Calendar Days], 'Table B'[Unique_ID], CurrentID),
    "Working Days", LOOKUPVALUE('Table B'[Working Days], 'Table B'[Unique_ID], CurrentID),
    BLANK()
)

Here is the expected result:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz