Forum Discussion

OPS-MLTSD's avatar
OPS-MLTSD
Icon for Post Patron rankPost Patron
4 years ago
Solved

DAX equivalent to %LIKE% in sql - to count IDs

Hello,   I am trying to do a simple count in Power BI using dax.    In sql, I would simply use this function to pull my data:   Select count (distinct Client_ID) Client_ID from table Where st...
Need Help
  • hnguy71's avatar
    hnguy71
    4 years ago

    Hi OPS-MLTSD ,

     

    You may be trying to compare a date field against a string value. This should work:

    CALCULATE(
    COUNT(clients[CLIENT_ID]), 
        SEARCH("UNIVERSITY", clients[School], 1, 0) > 0,
        clients[start_date] >= DATE(2021, 03, 16)
)
hnguy71's avatar
hnguy71
Icon for Super User rankSuper User
4 years ago
SEARCH("University",clients[School],1,0) > 0
  • OPS-MLTSD's avatar
    OPS-MLTSD
    Icon for Post Patron rankPost Patron
    4 years ago

    thank you, I was able to get this part to work: 

    University Students =
    CALCULATE(
    COUNT(clients[CLIENT_ID]),
    SEARCH("University",clients[School],1,0) > 0)
    But I could not add the date part, do you know how I can get this part to work in this dax? clients[start_date]>= '2021-03-16'. I only want results for clients from March 16  2021 and onwards, if you coul please let me know, I would really appreciate that. Thank you
    • davehus's avatar
      davehus
      Icon for Memorable Member rankMemorable Member
      4 years ago
      Try this. 
       
      CALCULATE(
      COUNT(clients[CLIENT_ID]),Filter(
      SEARCH("University",clients[School],1,0) > 0&&clients[start_date]>= '2021-03-16')) 
      • OPS-MLTSD's avatar
        OPS-MLTSD
        Icon for Post Patron rankPost Patron
        4 years ago

        thank you, for some reason, this is not working for me, I copied and pasted this exact dax