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

[OPS-MLTSD]

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

[hnguy71]

SEARCH("University",clients[School],1,0) > 0

[OPS-MLTSD]

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]

Try this. 

CALCULATE(

COUNT(clients[CLIENT_ID]),Filter(

SEARCH("University",clients[School],1,0) > 0&&clients[start_date]>= '2021-03-16')) 

[OPS-MLTSD]

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

[hnguy71 · Accepted solution]

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

[OPS-MLTSD]

@hnguy71 quick question, what is I wanted to search of two items, "Unniversity" and "College"? Would I use something like this?

CALCULATE(
    COUNT(clients[CLIENT_ID]), 
        OR(SEARCH("UNIVERSITY", "College", clients[School], 1, 0)) > 0,
        clients[start_date] >= DATE(2021, 03, 16)
)

[OPS-MLTSD]

it worked! thank you so much 🙂

[davehus]

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.