Forum Discussion
Helper IICount if DAX measure needed for 'Contains' text filter
I need to create a measure that will calculate the rows containing text, but the text in one cell has many other wordds. I
I can use the advanced filter option for 'Contains' and it will populate the correct amount including all text that contains atleast the desired text:
For example
I need to create a dax measure that allows me to countIF the text contains "blocking"
I have a column that contains the data:
Blocking
Exclusion
Blocking;Exclusion;Services
Blocking;Services;Debt
So all cells containing ATLEAST 'Blocking' should = 3
I have tried:
Calculate(
[TotalClients],filter ( 'table','table, signposted IN {"Blocking"} )
)
Any ideas?
Hi nmckeown1,
Try this:
VAR BlockingFilter = FILTER ( VALUES ( 'Table'[sign_posting] ), SEARCH ( "Blocking", 'Table'[sign_posting],, -1 ) <> -1 ) RETURN CALCULATE ( [Total Clients], BlockingFilter )(P.S.: Don't filter a whole table in your FILTER function. That tends to result in poor performance because you're retrieving columns you don't actually need.)
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.
3 Replies
Wilson_Memorable Member
Hi nmckeown1,
Try this:
VAR BlockingFilter = FILTER ( VALUES ( 'Table'[sign_posting] ), SEARCH ( "Blocking", 'Table'[sign_posting],, -1 ) <> -1 ) RETURN CALCULATE ( [Total Clients], BlockingFilter )(P.S.: Don't filter a whole table in your FILTER function. That tends to result in poor performance because you're retrieving columns you don't actually need.)
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.
