PowerBI--Custom Column--Multiple Condition IF statements

1
votes

Beginner here, desperate for your help.

Basically, I need a new column to take the value of either column shown in the image, unless both columns are null. If both are null, then the new column should say "No discipline entered".

My version of PowerBI only has add a custom column option in the edit queries window. I have tried working the below solutions, but I obviously have a concept error and not using the solutions appropriately. I appreciate your patience and assistance!

Custom Formula Columns

4
powerbidax

4 Answers

3
votes

The reason you are getting "Expression.Error: The name..." errors is because your are trying to enter DAX formulas in Power Query editor. Power Query uses a different language called "M", and does not recognize DAX. You can solve this problem in 2 ways:

1) Exit query editor, and in PowerBI window, go to tab "Modeling" and create "New Column". Enter DAX formulas there;

2) If you prefer to solve the problem in Power Query, create a custom column there and enter this "M" formula:

each List.First(List.RemoveNulls({[PIDISK], [PI_DISK]}), "No Disk Entered"))

How the formula works: List.RemoveNulls removes nulls from the list of columns you provide. Then it picks the first value from the result; if there is none, it uses default option ("no disk entered"). Just make sure that your NULLs are really nulls.

1
votes

Whats up? Either of these should work depending on whether or not you have "null" strings or blank() values:

"null"

New Column =
SWITCH (
    TRUE (),
    AND ( [PIDISC] = "null", [PI_DISC] = "null" ), "NO DISC ENTERED",
    [PIDISC] = "null", [PI_DISC],
    [PI_DISC] = "null", [PIDISC]
)

Tested:

enter image description here

BLANK()

New Column =
SWITCH (
    TRUE (),
    AND ( ISBLANK([PIDISC]), ISBLANK([PI_DISC]) ) , "NO DISC ENTERED",
    ISBLANK ( [PIDISC] ), [PI_DISC],
    ISBLANK ( [PI_DISC] ), [PIDISC]
)

Hope this helps!

0
votes

If you'd like to do this in DAX, I recommend using the SWITCH ( TRUE() ...) method in lieu of nested if statements (which this article explains beautifully). I believe this should produce the desired result; based upon your screenshot I assumed those nulls were text strings vs. NULL. If it is a true NULL, PowerBI uses BLANK().

= SWITCH (
    TRUE (),
    [PIDISC] <> "null", [PIDISC],
    [PI_DISC] <> "null", [PI_DISC],
    "no disc entered"
)

The first condition that evaluates to TRUE() will take precedence.

0
votes

A nested IF isn't too bad for this:

IF([PIDISC] = "null" && [PI_DISC] = "null",
   "NO DISC ENTERED,
   IF([PIDISC] = "null",
      [PI_DISC],
      [PIDISC]
   )
)

or

IF([PIDISC] <> "null",
   [PIDISC],
   IF([PI_DISC] <> "null",
      [PI_DISC],
      "NO DISC ENTERED"
   )
)

If those are blanks rather than text "null", then it might look a bit different.

IF(ISLBANK([PIDISC]) && ISBLANK([PI_DISC]),
   "NO DISC ENTERED,
   IF(ISLBANK([PIDISC]),
      [PI_DISC],
      [PIDISC]
   )
)

or

IF(NOT(ISLBANK([PIDISC])),
   [PIDISC],
   IF(NOT(ISBLANK([PI_DISC]),
      [PI_DISC],
      "NO DISC ENTERED"
   )
)

EDIT:

Since you are trying to work in the query editor, your M language custom column might look like this:

if [PIDISC] = "null" and [PI_DISC] = "null"
 then "NO DISC ENTERED"
else if [PIDISC] = "null" then [PI_DISC]
else if [PI_DISC] = "null" then [PIDISC]
else "Else Condition"

or

if [PIDISC] = "" and [PI_DISC] = "" then "NO DISC ENTERED"
else if [PIDISC] = "" then [PI_DISC]
else if [PI_DISC] = "" then [PIDISC]
else "Else Condition"