Forum Discussion
DAX Function on FILTER with like?
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.
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
10 Replies
Lcouch You can use CONTAINSSTRING. Doesn't do the % wildcards but otherwise might work. CONTAINSSTRING function (DAX) - DAX | Microsoft Learn
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
CONTAINSSTRING takes 2 parameters:
CONTAINSSTRING(<within_text>, <find_text>)
So try:
CALCULATE(COUNTA('VIEW_ALMMTaskRequestDetails'[CreatedBy]), CONTAINSSTRING('VIEW_ALMMTaskRequestDetails'[ResourceType], "Servicebus"))
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 () )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 () )
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.
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
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
evaluate
SUMMARIZECOLUMNS('Contract'[Business Unit],
FILTER('Contract',CONTAINSSTRING('Contract'[Business Unit],"AUS"))) --->>This is equivalent to like condition in SQL.
