CODEWITHSUNDEEP
daxlogicmultiple-conditions
Tom Johnson
Tom Johnson

Reputation: 1

DAX How to Return Value Based on Business Logic

I have a data set that is pulled together from different sources, but on one table. I need a formula that will help me evaluate and choose the correct column.

3 Text Columns

  1. USI
  2. 4Ever_Code
  3. Kit_Code

The columns could all have values in them or could all be null. I want to create a final column called Final_Code that follows this logic.

Test:

  1. If 4EverB is not blank or null then supply that value for the row.
  2. If 4EverB is blank or null the check Kit_Code if it exists use that value for the row
  3. If both 4EverB is null or blank and Kit_code is null or blank, then the USI should be chosen for the row.

enter image description here

Upvotes: 0

Views: 22

Answers (1)

Michael Anckaert
Michael Anckaert

Reputation: 953

You should do this transformation with Power Query, you can use the following M-code:

let
Source = YourTable, // Replace 'YourTable' with your actual table name
AddFinalCode = Table.AddColumn(Source, "Final_Code", each 
    if [4Ever_Code] <> null and Text.Trim([4Ever_Code]) <> "" then [4Ever_Code]
    else if [Kit_Code] <> null and Text.Trim([Kit_Code]) <> "" then [Kit_Code]
    else [USI],
    type text
)

in AddFinalCode

Upvotes: 0

Related Questions