Forum Discussion

Post 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

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

OPS-MLTSD

Post 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

Memorable 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
Post Patron
4 years ago
thank you, for some reason, this is not working for me, I copied and pasted this exact dax
- hnguy71
  Super User
  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) )
  - OPS-MLTSD
    Post Patron
    4 years ago
    it worked! thank you so much 🙂

Forum Discussion

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

Related content

DAX equivalent of SQL LIKE

Counting nulls

SUMIF Equivalent in DAX

Equivalent to LIKE

Equivalent de NB.SI