Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
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 start_date >= '2021-03-16'

AND School LIKE '%University%'

 

i was wondering how I can do the same count in Power BI? I have been reading about the dax called "SEARCH" and I tried this method but I think I am missing something

 

University Students =
CALCULATE(
COUNT(clients[CLIENT_ID]),
SEARCH("University",clients[School],1,0),
DATESBETWEEN(clients[start_date]>= '2021-03-16')))+0
 
thank you
Need Help
  • hnguy71's avatar
    hnguy71
    4 years ago

    Hi Anonymous ,

     

    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)
)

8 Replies

  • hnguy71's avatar
    hnguy71
    Icon for Super User rankSuper User
    4 years ago
    SEARCH("University",clients[School],1,0) > 0
    • Anonymous's avatar
      Anonymous
      Not applicable
      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')) 
  • davehus's avatar
    davehus
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Hi, 

     

    Have you tried just adding a column in PowerQuery and use a conditional column to say if column contains University then 1 else 0. Then use this column in place of the search function using and Filter(&&) clause at the end of the calculate measure.