Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Match() function in DAX?

Does power BI has some sort of match function where I pass a column, a list of values from that column, and put those values as a certain category?

For instance, the values from a col called Produtcts are: Honda, BMW, Ferrari, etc I want to group into a column called category as Car, the others values, as others.

 

In another BI tool i use a function like this Category = If(match("Produtcs", ("Honda","BMW","Ferrari"))>0, Car,Others)) 

 

Does Dax has sometning like that?

 

Thank you!

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi,

     

    You could use the IN operator to do that kind of "match".

    In your example the new column could be created with the following DAX statement:

     

    Category =

    IF (

         'Table'[Products] IN { "Honda", "BMW", "Ferrari"},

         "Car",

         "Others"
    )

     

    Br,

    Magnus

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      is there function equivalent to "IN with wildcard"? for example match x IN { "foo*, fo-o*", "bar"}

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Old question, but never answered.  I have 1.8 million rows and 1400 distinct values.  It simply isn't practical to list them on the command line, not to mention, it's an easy match -- the first two or three characters will get me where I need to be.  Awk is looking more promising.