Calculated Column

[zaforir2002]

Is there any other easy way to write the following DAX expression...

EthnicitySummarised:=
IF (
  FIND ( "Asian", [Ethnicity], 1, BLANK () ),
  "Asian",
  IF (
    FIND ( "Black", [Ethnicity], 1, BLANK () ),
    "Black",
    IF (
      FIND ( "White", [Ethnicity], 1, BLANK () ),
      "White",
      IF (
        FIND ( "Unknown", [Ethnicity], 1, BLANK () ),
        "Unknown",
        IF ( FIND ( "Chinese", [Ethnicity], 1, BLANK () ), "Chinese", "Other" )
      )
    )
  )
)

[dataX · Accepted solution]

@zaforir2002, I think you can make it more clean by using Switch and Search. Search is also case insensitive and I think is a better approach.

Ethnicitycol= switch(
TRUE(),
SEARCH("ASIAN",'Sheet3'[ETHNICITY],1,blank())>0,"ASIAN",
SEARCH("LATINO",'Sheet3'[ETHNICITY],1,blank())>0, "LATINO",
"UNKN0WN"

)

[Anonymous]

Hi @zaforir2002, it looks like you are trying to return the [Ethnicity] value if it's part of a certain subset of values, and classify anything outside that subsite as "Other". If that's accurate, then you could use the IN operator:

EthnicitySummarised = IF([Ethnicity] IN {"Asian","Black","White","Unknown","Chinese"}, [Ethnicity], "Other")

Hope that helps! 🙂

[zaforir2002]

Hi JaredK, thanks for your reply, but my situation is bit different.... I'm finding the specific words out of a long sentence to construct the new calculated column....

[Phil_Seamark]

I'm wondering the FIND function is being used because he is looking for the existance of that text within a longer string.  I think the IN function needs an exact match. Just need @zaforir2002 to confirm.

[zaforir2002]

Phil_Searmark is right... I'm finding those specific words out of a long string... my existing DAX expression is working... but thought if is there any other way to write that expression as it it bit long and massy.... 🙂

[parry2k]

Did you tried adding conditional column? In that case  you don't need to write DAX and it is much cleaner and not messy, as you mentioned.

[parry2k]

Other though, why not add conditional column rather than writing DAX? 

[zaforir2002]

Hi parry2k, the tabular model I'm working on has many partitions, thereforeign if I add an extra column to one partition then I need to do that for other partitions too, which I don't want...

[parry2k]

When I said add conditional column, i mean to add in Power BI using "Edit Queries". 

- Click Edit Queries

- Goto Add Columns Tab

- There is option called "Conditional Column" and this is what I'm talking about.

Thanks,

P

[zaforir2002]

Sorry parry2K, bit of confusion here... although I'm in a power BI community... I'm not using the power BI yet... this is for the existing solution that the organisation has where I'm working... they are planing to use power BI within next couple of months... until then they asked me to add this additional column to their existing tabular model... hence I'm here... although my written DAX expression working as it men to... but I thought if is there any other way to write it then I would...

[parry2k]

Aha , that make sense, thanks for clarifying. I believe in that case your expression seems to be the way to go.