topic Re: Using wildcard characters in a SWITCH function in DAX Commands and Tips

Using wildcard characters in a SWITCH function

Joachim2108 — Wed, 10 Mar 2021 14:15:33 GMT

Hi!

I'm struggling to create a new collumn with wildcard characters, based on certain values in another collumn using the SWITCH function.

The products in the original collumn are grouped between 100-900 (e.g. 112, 320 etc.). I want to group these into smaller categories.

In plain english I want for example: if the ProductGroup starts with 3, i.e. is between 300-399 (data type = text), it needs to be categorised as "Tools". I used wilcard characters in the DAX query, however it doesn't seem to recognise them. A piece of the query is below - what am I doing wrong? Thanks in advance!

ProductCategory=

SWITCH ( TRUE (),

'Storage’[ProductGroup] = "3**", "Tools",

'Storage’[ProductGroup] = "4**", "Service",

'Storage’[ProductGroup] = "5**", "Transport",

"Other" )

Re: Using wildcard characters in a SWITCH function

AntrikshSharma — Wed, 10 Mar 2021 14:52:17 GMT

Joachim2108 Wildcards are not supported in DAX afaik, you will have to use either

https://dax.guide/containsstring/

https://dax.guide/containsstringexact/

Re: Using wildcard characters in a SWITCH function

Joachim2108 — Thu, 11 Mar 2021 07:45:52 GMT

If I can't use wildcards, could I combine the SWITCH statement with a LEFT statement?

Re: Using wildcard characters in a SWITCH function

Joachim2108 — Thu, 11 Mar 2021 14:27:15 GMT

Hi all, the solution turned out to be:

IF (LEFT ( Storage[Productgroup) = LEN(1)) = "3", "Tools",

IF (LEFT ( Storage[Productgroup], LEN(1)) = "4", "Service",

IF (LEFT ( Storage[Productgroup], LEN(1)) = "5", "Transport"