Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Converting Excel Formula to Power BI

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!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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.