DAX Function on FILTER with like?

[Lcouch]

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

[v-xinruzhu-msft]

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.}

[Lcouch]

@v-xinruzhu-msft 

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

[v-xinruzhu-msft · Accepted solution]

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

[Manoj_Nair]

@Lcouch - Try this calculated column DAX as below shown.

[Manoj_Nair]

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

[Lcouch]

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

[Greg_Deckler]

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

[Lcouch]

@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

[Greg_Deckler]

@Lcouch

CONTAINSSTRING takes 2 parameters:

CONTAINSSTRING(<within_text>, <find_text>)

So try:

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