Forum Discussion

villa1980's avatar
villa1980
Icon for Resolver II rankResolver II
1 year ago
Solved

DAX SWITCH not contain "*"

Hi all,

I have a DAX formula that brings back 1 when a criteria is met and 0 when none of those criteria are met.
What I would like to do is exclude anything with VEHICLEREGISTRATION that contains * or any special character.
When I try to include this in the switch statement it still includes it.
Any ideas how I can change the switch statement so that it says if VEHICLEREGISTRATION does not contain * or any other special character then 1
I am using a SWITCH statement which is below.

Filter Validation = SWITCH(
    TRUE(),
    Not(CONTAINSSTRING('EXTR_STG_FOCUS APPOINTMENTS'[VEHICLEREGISTRATION],"*")),1,
    ('EXTR_STG_FOCUS APPOINTMENTS'[Vehicle_Reg_Count] >2 && 'EXTR_STG_FOCUS APPOINTMENTS'[Vehicle_Reg_Count] <=7 ) && ISBLANK('EXTR_STG_FOCUS APPOINTMENTS'[APPOINTMENTTEXT]),1,
   
     NOT(ISBLANK('EXTR_STG_FOCUS APPOINTMENTS'[APPOINTMENTTEXT])),1,
    0
    )
Need Help
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    villa1980 

    Unfortunately, Power BI DAX does not natively support advanced pattern matching or regular expressions.

    However, you can achieve this by explicitly checking for each unwanted character using CONTAINSSTRING or similar functions.

     

    Filter Validation = 
    SWITCH(
    TRUE(),

    NOT(
    CONTAINSSTRING('EXTR_STG_FOCUS APPOINTMENTS'[VEHICLEREGISTRATION], "*") 
    || CONTAINSSTRING('EXTR_STG_FOCUS APPOINTMENTS'[VEHICLEREGISTRATION], "#") 
    || CONTAINSSTRING('EXTR_STG_FOCUS APPOINTMENTS'[VEHICLEREGISTRATION], "$") 
    || CONTAINSSTRING('EXTR_STG_FOCUS APPOINTMENTS'[VEHICLEREGISTRATION], "@")
    ) &&
    (
    ('EXTR_STG_FOCUS APPOINTMENTS'[Vehicle_Reg_Count] > 2 
    && 'EXTR_STG_FOCUS APPOINTMENTS'[Vehicle_Reg_Count] <= 7) 
    && ISBLANK('EXTR_STG_FOCUS APPOINTMENTS'[APPOINTMENTTEXT])
    ),
    1,
    NOT(ISBLANK('EXTR_STG_FOCUS APPOINTMENTS'[APPOINTMENTTEXT])), 1,
    0
    )

    💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

     

3 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago

    villa1980 

    Unfortunately, Power BI DAX does not natively support advanced pattern matching or regular expressions.

    However, you can achieve this by explicitly checking for each unwanted character using CONTAINSSTRING or similar functions.

     

    Filter Validation = 
    SWITCH(
    TRUE(),

    NOT(
    CONTAINSSTRING('EXTR_STG_FOCUS APPOINTMENTS'[VEHICLEREGISTRATION], "*") 
    || CONTAINSSTRING('EXTR_STG_FOCUS APPOINTMENTS'[VEHICLEREGISTRATION], "#") 
    || CONTAINSSTRING('EXTR_STG_FOCUS APPOINTMENTS'[VEHICLEREGISTRATION], "$") 
    || CONTAINSSTRING('EXTR_STG_FOCUS APPOINTMENTS'[VEHICLEREGISTRATION], "@")
    ) &&
    (
    ('EXTR_STG_FOCUS APPOINTMENTS'[Vehicle_Reg_Count] > 2 
    && 'EXTR_STG_FOCUS APPOINTMENTS'[Vehicle_Reg_Count] <= 7) 
    && ISBLANK('EXTR_STG_FOCUS APPOINTMENTS'[APPOINTMENTTEXT])
    ),
    1,
    NOT(ISBLANK('EXTR_STG_FOCUS APPOINTMENTS'[APPOINTMENTTEXT])), 1,
    0
    )

    💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

     

    • villa1980's avatar
      villa1980
      Icon for Resolver II rankResolver II
      1 year ago

      Thank-you for the quick, although it has worked for the majority it has not worked for some of the rows..from the below this should return a 1 as the vehicle registration is 7 and Text is Blank, but it returns 0

       

    • villa1980's avatar
      villa1980
      Icon for Resolver II rankResolver II
      1 year ago

      Sorry, I worked out why it wasn't counting certain rows, looking at your Formula it did not include ~ when looking at special characters within it, adding that to it was correct

       

      CONTAINSSTRING('EXTR_STG_FOCUS APPOINTMENTS'[VEHICLEREGISTRATION], "*")
      CONTAINSSTRING('EXTR_STG_FOCUS APPOINTMENTS'[VEHICLEREGISTRATION],"~*")