Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
OPS-MLTSD
Post Patron
Post Patron

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


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

View solution in original post

8 REPLIES 8
hnguy71
Memorable Member
Memorable Member

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


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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

Try this. 
 
CALCULATE(
COUNT(clients[CLIENT_ID]),Filter(
SEARCH("University",clients[School],1,0) > 0&&clients[start_date]>= '2021-03-16')) 

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

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


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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

 

it worked! thank you so much 🙂

davehus
Memorable Member
Memorable Member

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.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.