Forum Discussion
Multiple Conditions into 1 Calculated Column
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]))
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
6 Replies
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 HafizHello 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)RETURNSWITCH(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
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.
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 ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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?
