Filter for part of string value within column by column in another table

[Anonymous]

Good Afternoon,

I've been stuck on this DAX issue for days and would appreciate your help.

I have a table [STAR Active Clients] containing a list of services each client purchases from us. These services are separated by commas, as pictured below.

I want to filter the table this service list is in by individual service (value within the string).
I have another table [STAR Active Jobs for Filter] only containing the list of services, as pictured below:

I added a measure to [STAR Active Jobs for Filter] and placed this measure in a slicer. Problem is, the below DAX only filters for clients from [STAR Active Clients] that contain only the  1 single service rather than also including clients that contain the service within the string list of services. I need to slice [STAR Active Clients] for each service contained within the string. How can I change the below DAX to accomplish this? (I have 2 versions of the DAX calculation that provide the same, unwanted result.)

Thank you!

[Anonymous · Accepted solution]

@tamerj1 , I appreciate your help. I was able to find a solution by changing my relationships rather than using a measure or calculated column.

'STAR Active Clients' table has the list of services with ClientID being my key (unique value).

'STAR Active Jobs' has all jobs [Engagement Description] (in separate rows) associated with each ClientID, causing multiple values for each ClientID.

'STAR Active Jobs for Filter' is just the list of 78 job/engagement types. 
I set up the below relationships with 'Star Active Jobs' and 'STAR Active Clients' connected by ClientID. My visuals now display the correct clients when I select a service from the slicer.

I both love and hate when the solution is much simpler than I try to make it!

[tamerj1]

@Anonymous 

I got the feeling that it should be the other way around but I wanted to make sure. However, I suppose that visual is based on the active clients table. If so, you may try to iterate over the active jobs table to compare the value of the current filter context with each value of the filter table

Star Services =
IF (
    SUMX (
        'Star Active Jobs',
        FIND (
            UPPER ( 'Star Active Jobs'[BKD Services] ),
            UPPER ( MAX ( 'Star Active Clients'[BKD Services] ) ),
            ,
            0
        )
    ) > 0,
    1,
    0
)

[Anonymous]

When I use this DAX, swapping the tables, I get the same result.

I was wondering if I need to utilize CONTAINS or something of the like to search the whole string of 'STAR Active Clients'[BKD Services] rather than only finding standalone values.

[tamerj1]

You mean CONTAINSTRING. I guess it might work but you may first try SEARCH instead of find. And no need to use UPPER

[Anonymous]

I'm not able to figure out passing columns into CONTAINSSTRING... Would my purpose be better served using a calculated column rather than a measure? I wasn't sure how dynamic a calculated column would be since I have 78 different services to filter by.

[tamerj1]

@Anonymous 

Or you're just placing the measure in the filter of the visual to filter only the rows that contain at least one of the values of the filter table?

[tamerj1]

Hi @Anonymous 

this a measure right? Not a calculated column?

what exactly this measure is supposed to count? How does your visual look like?

[Anonymous]

Yes, I'm using a measure rather than a calculated column.

I am mapping my [STAR Active Clients] in an Azure Map visual. I want to filter the client location plot points based on the services purchased. I'm using 'STAR Active Jobs for Filter'[BKD Services] in a slicer to select the clients with each individual service on [STAR Active Clients]. In the below picture, my slicer is only selecting clients that have only "AOS" as a service rather than also including those with "AOS" as 1 of many services in the list.