Converting Excel Formula to Power BI

[Anonymous]

Hi All,

Need assistance in converting this excel function to Dax  Power Bi. 

Also, if anyone can help me to simplify the excel function it woudl be great as well

=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"WELCOME","SUBSCRIBER","WELLCOME","WECOME"},M2)))=1,"WELCOME",IF(SUMPRODUCT(--ISNUMBER(SEARCH({"NHS","KEYWORKER","KEY WORKER"},M2)))=1,"NHS/KEYWORKER",IF(SUMPRODUCT(--ISNUMBER(SEARCH({"STAFF"},M2)))=1,"STAFF","NO VOUCHER")))

output:

True="WELCOME", "NHS/KEYWORKER", "STAFF"

False="NO VOUCHER"

Thank you a lot!

[Anonymous]

Hi @Anonymous ,

I suggest you to try this code to create a calculated column.

Convert Formula = 
SWITCH (
    TRUE (),
    'Table'[M Column in Excel] IN { "WELCOME", "SUBSCRIBER", "WELLCOME", "WECOME" }, "WELCOME",
    'Table'[M Column in Excel] IN { "NHS", "KEYWORKER", "KEY WORKER" }, "NHS/KEYWORKER",
    'Table'[M Column in Excel] IN { "STAFF" }, "STAFF",
    "NO VOUCHER"
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous 

Thank you for your help.

I tried the solution you give me but I got into issue saying "A single value for column 'M Column' in table 'Table' cannot be determined. Do you have any idea how can I sort it out?

Thank you.

[Anonymous · Accepted solution]

Hi @Anonymous ,

I think you use my code above to create a measure. I can reproduce the error in my side.

As I mentioned above, the code above should be used to create a calcualted column, instead of a measure. Measure will not be load to data model, it will return results only you add it into visual.

For more details about measure and calculated column, you may refer to this blog.

If you still want to create a measure, you can try this code. You need to use MAX() function to catch text type value.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.