topic Re: DAX Function on FILTER with like? in DAX Commands and Tips

DAX Function on FILTER with like?

Lcouch — Fri, 02 Dec 2022 22:57:01 GMT

Hello,

Can I create a measure when I filter on a value like I have Servicebus below- use Like %Servicebus% as we do in SQL?

The column in the tables has various values with this word and I need all of them returned. I did get an error with typing it as above.

Total Of ALMM Service Bus Actions =

CALCULATE(COUNTA('VIEW_ALMMTaskRequestDetails'[CreatedBy]), FILTER('VIEW_ALMMTaskRequestDetails', 'VIEW_ALMMTaskRequestDetails'[ResourceType] ="Servicebus")) --- see above need to get a count of all with this word in it

Thank you!!!

Re: DAX Function on FILTER with like?

Greg_Deckler — Fri, 02 Dec 2022 23:18:01 GMT

Lcouch You can use CONTAINSSTRING. Doesn't do the % wildcards but otherwise might work. CONTAINSSTRING function (DAX) - DAX | Microsoft Learn

Re: DAX Function on FILTER with like?

Lcouch — Fri, 02 Dec 2022 23:26:58 GMT

Greg_Deckler -

I get an error when I tried:

Total Of ALMM Service Bus Actions =

CALCULATE(COUNTA('VIEW_ALMMTaskRequestDetails'[CreatedBy]), FILTER('VIEW_ALMMTaskRequestDetails', ('VIEW_ALMMTaskRequestDetails'[ResourceType] CONTAINSSTRING("Servicebus"))

Also tried:

Total Of ALMM Service Bus Actions =

CALCULATE(COUNTA('VIEW_ALMMTaskRequestDetails'[CreatedBy]), EVAULATE ROW('VIEW_ALMMTaskRequestDetails', ('VIEW_ALMMTaskRequestDetails'[ResourceType] CONTAINSSTRING("Servicebus"))

(removed the filter even though I need that filter) but it didnt like Evaluate

Re: DAX Function on FILTER with like?

Greg_Deckler — Fri, 02 Dec 2022 23:41:16 GMT

Lcouch

CONTAINSSTRING takes 2 parameters:

CONTAINSSTRING(<within_text>, <find_text>)

So try:

CALCULATE(COUNTA('VIEW_ALMMTaskRequestDetails'[CreatedBy]), CONTAINSSTRING('VIEW_ALMMTaskRequestDetails'[ResourceType], "Servicebus"))

Re: DAX Function on FILTER with like?

Manoj_Nair — Sat, 03 Dec 2022 01:03:21 GMT

Lcouch - Try this calculated column DAX as below shown.

Re: DAX Function on FILTER with like?

Anonymous — Mon, 05 Dec 2022 08:20:56 GMT

Hi Lcouch

You can try the following code:

Total Of ALMM Service Bus Actions = COUNTAX(FILTER('VIEW_ALMMTaskRequestDetails', CONTAINSSTRING('VIEW_ALMMTaskRequestDetails'[ResourceType] ,"Servicebus")) ,[CreatedBy])

^{Best Regards,}

^{Yolo Zhu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

Re: DAX Function on FILTER with like?

Manoj_Nair — Mon, 05 Dec 2022 15:17:43 GMT

Lcouch

Oops!! I missed copying the DAX. Let me know if this fixes your problem

Total Of ALMM Service Bus Actions = IF ( SEARCH ( "Servicebus", VIEW_ALMMTaskRequestDetails[ResourceType], 1, 0 ) > 0, "Servicebus", BLANK () )

Re: DAX Function on FILTER with like?

Lcouch — Tue, 06 Dec 2022 00:25:12 GMT

Hi!

Thank you Manoj_Nair but it doesnt count CreatedBy

So my current function is

Total Of ALMM Service Bus Actions =

CALCULATE(COUNTA('VIEW_ALMMTaskRequestDetails'[CreatedBy]), FILTER('VIEW_ALMMTaskRequestDetails', 'VIEW_ALMMTaskRequestDetails'[ResourceType] ="Servicebus"))

and the one you provide is below but no count of createdy by

Total Of ALMM Service Bus Actions = IF ( SEARCH ( "Servicebus", VIEW_ALMMTaskRequestDetails[ResourceType], 1, 0 ) > 0, "Servicebus", BLANK () )

Re: DAX Function on FILTER with like?

Lcouch — Tue, 06 Dec 2022 00:27:43 GMT

Anonymous

When I tried

Total Of ALMM Service Bus Actions = COUNTAX(FILTER('VIEW_ALMMTaskRequestDetails', 'VIEW_ALMMTaskRequestDetails'[ResourceType] ="Servicebus") ,[CreatedBy])

I get the same result as when I use

Total Service Bus Provisions =

CALCULATE(COUNTA('VIEW_SBFuncAppWebAppEventHubs'[Name]), FILTER('VIEW_SBFuncAppWebAppEventHubs', 'VIEW_SBFuncAppWebAppEventHubs'[Type]="ServiceBus") )

it gives me the count.. but for any types that have 'ServiceBus' as part of the name but it is not the only spelling.. it doesnt return them

Re: DAX Function on FILTER with like?

Anonymous — Tue, 06 Dec 2022 08:05:18 GMT

Hi Lcouch

The code you displayed is not the same as mine:

Total Of ALMM Service Bus Actions = COUNTAX(FILTER('VIEW_ALMMTaskRequestDetails', CONTAINSSTRING('VIEW_ALMMTaskRequestDetails'[ResourceType] ,"Servicebus")) ,[CreatedBy])

Maybe you can try the code above again

Best Regards!

Yolo Zhu

Re: DAX Function on FILTER with like?

Rohit_Patil7399 — Wed, 08 Jan 2025 11:52:10 GMT

evaluate
SUMMARIZECOLUMNS('Contract'[Business Unit],
FILTER('Contract',CONTAINSSTRING('Contract'[Business Unit],"AUS"))) --->>This is equivalent to like condition in SQL.