Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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)
)
SEARCH("University",clients[School],1,0) > 0
thank you, I was able to get this part to work:
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)
)
@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 🙂
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.