Forum Discussion

eeviste's avatar
eeviste
New Member
3 years ago
Solved

Compiling a new column value from multiple columns if only one of them has a value per row?

Hi all!

 

I am trying to create a new text column in Power BI desktop from four separate text columns within the same table.

For each data row, only one of these four columns will have a text value, while the other columns have a blank value. 

This is how the data look, and I am trying to combine the four to create 'DesiredColumn' on the right (in green font). 

I have tried creating various VAR _lookup, ISBLANK, if / then / else commands with no luck so far. Simply using '&' for two columns creates a (Blank) addition to the combo text result value. 😅 If anyone has any ideas, I'd be keen to hear them. I'm a new user so not very agile with DAX yet. Thanks for your time! ☕

 

Sample data:

 

AgeGroup1UnknownAgeUnknownAdultUnknownChildDesiredColumn
0-5 years   0-5 years
   Unknown childUnknown child
13-19 years   13-19 years
65 years and older   65 years and older
  Unknown adult Unknown adult
6-12 years   6-12 years
 Unknown age  Unknown age
0-5 years   0-5 years
0-5 years   0-5 years
65 years and older   65 years and older
13-19 years   13-19 years
 Unknown age  Unknown age
20-64 years   20-64 years
20-64 years   20-64 years
20-64 years   20-64 years
0-5 years   0-5 years
   Unknown childUnknown child
   Unknown childUnknown child

 

 

  • amitchandak's avatar
    amitchandak
    3 years ago

    eeviste , Try one of the two way to create a new columns

     

    coalease([AgeGroup1],[UnknownAge],[UnknownAdult],[UnknownChild])

     

     

    Switch(True() ,
    not(isblank([AgeGroup1])) && [AgeGroup1]<>"", [AgeGroup1],
    not(isblank([UnknownAge])) && [UnknownAge]<>"", [UnknownAge],
    not(isblank([UnknownAdult])) && [UnknownAdult]<>"", [UnknownAdult],
    not(isblank([UnknownChild])) && [UnknownChild]<>"", [UnknownChild])

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    eeviste , Try one of the two way to create a new columns

     

    coalease([AgeGroup1],[UnknownAge],[UnknownAdult],[UnknownChild])

     

     

    Switch(True() ,
    not(isblank([AgeGroup1])) && [AgeGroup1]<>"", [AgeGroup1],
    not(isblank([UnknownAge])) && [UnknownAge]<>"", [UnknownAge],
    not(isblank([UnknownAdult])) && [UnknownAdult]<>"", [UnknownAdult],
    not(isblank([UnknownChild])) && [UnknownChild]<>"", [UnknownChild])

    • eeviste's avatar
      eeviste
      New Member
      3 years ago

      Thanks heaps for your help, amitchandak ! 👍

       

      COALESCE did not work for me, but I got the SWITCH combo to work, thank you. In it though, for reasons I don't fully understand, I needed to swap around the variables and have AgeGroup1 last... Not sure why, but it works like this, so I am happy! 😄

       

      This is my code in the end, with a little addition (that I didn't include in the original request for help, for clarity) where I wanted to limit the rows that get the combined value to humans (another variable in the same table), and others would be "not human". 

       

      AgeGroup7 = IF( data[Subject] = "Human",
    SWITCH(
    TRUE(),
    NOT(ISBLANK(data[UnknownAge])) && data[UnknownAge] <> "", data[UnknownAge],
    NOT(ISBLANK(data[UnknownAdult])) && data[UnknownAdult] <> "", data[UnknownAdult],
    NOT(ISBLANK(data[UnknownChild])) && data[UnknownChild] <> "", data[UnknownChild],
    NOT(ISBLANK(data[AgeGroup1])) && data[AgeGroup1] <> "", data[AgeGroup1],
    "not human"
    ), "not human")

       

      I think I will mark yours as the solution, even though there were some tweaks that were needed. It still cracked it for me. Thanks heaps again for your help! 👍