How to Use multiple selected values and contains string together

[Anonymous]

I have a Project Table with a column which contains team names with comma seperated values(A,B ; A,B,C ; B,C). 
This table is connected via Project ID column to another table "Project Score" .

I have created a manual table with single column of individual team names.

I will use this column in a slicer

Then there needs to be a card visual which shows the sum of project score  based on the selected values in the slicer. 
If the user selects A & B. then the sum should include score of all projects in which either of these or both of these team were a part. The Sum should not include the value twice if both teams are there.

I was trying this using VALUES and CONTAINSSTRING, but could not achieve it. Looking for some guidance.

[OwenAuger]

You're welcome @Anonymous  🙂
Understood - in this case it may be more convenient to apply Team, Region etc filters using disconnected tables, along with DAX to transfer the filter to the Project table.

Would it be possible to replace the commas with vertical bars i.e. "|"? If so, you could make use of the PATHCONTAINS function as convenient way of testing membership of a particular value in a delimited list.

I have attached a sample PBIX using this method for Team and Region dimensions.

Team and Region are disconnected tables, and in the Project table, these are represented by vertical bar-delimited lists.

Assuming that you need to filter measures, you can use code similar to below to apply the filters to a given measure:

Score Sum Filtered by Team & Region = 
-- Use Team[Team] values to construct a filter for the Project[Team] column
VAR TeamValues =
    VALUES ( Team[Team] )
VAR TeamConcatValues =
    VALUES ( Project[Team] )
VAR TeamFilter =
    GENERATE (
        TeamConcatValues,
        FILTER (
            TeamValues,
            PATHCONTAINS ( Project[Team], Team[Team] )
        )
    )
-- Use Region[Region] values to construct a filter for the Project[Region] column
VAR RegionValues =
    VALUES ( Region[Region] )
VAR RegionConcatValues =
    VALUES ( Project[Region] )
VAR RegionFilter =
    GENERATE (
        RegionConcatValues,
        FILTER (
            RegionValues,
            PATHCONTAINS ( Project[Region], Region[Region] )
        )
    )
VAR FilteredMeasure =
    CALCULATE ( 
        [Score Sum],
        TeamFilter,
        RegionFilter
    )
RETURN
    FilteredMeasure

I also created a calculation group 'Filter Calculation Group' with calculation item "Apply Team and Region Filter to Project" that applies this logic to any measure. This might be more convenient than writing multiple measures with similar code.

Are you able to apply a similar method in your model?

I would be interested in how well this performs with your actual data, given the length of your lists.

Regards,

Owen

[Anonymous]

Thats exactly what I was looking for. Seems to be working fast enough for me. 
Thanks a lot !!

[OwenAuger · Accepted solution]

Hi @Anonymous 

As you effectively have a many-to-many relationship between Project and Team, I would recommend creating a bridge table 'Project Team' that contains the combinations of Project ID and Team.

You would then create relationship between Project and 'Project Team' (bidirectional), and between Team and 'Project Team' (single directional).

While it is possible to use CONTAINSSTRING or something similar, it's generally preferable to use physical relationships, for performance reasons as well as avoiding having to write DAX to simulate the relationship.

To give an example, if your Project table looks like this:

then 'Project Team' would look like this (can be created by taking the Project table and splitting the Team column across rows):

The data model would look like this:

Filters on the Team table will propogate via 'Project Team' to Project, and then to 'Project Score'.

Simple PBIX attached.

Regards,

Owen

[Anonymous]

Thankyou @OwenAuger  for your quick response. I really appreciate it.

The number of comma seperated values can be around 20 in some rows. Moreover, going ahead there are 3 more columns of similar type where comma seperated values are present (Regions involved, Tools used etc.). We need to use these in slicers as well. 
I had tried this approach but this will involve huge number of steps in power query to seperate values in different columns and then unpivoting those. These steps are consuming lot of time while refreshing.  That is why I was hoping to achieve this somehow using DAX.

I used a measure like this : 
Team Slicer =

VAR mycount = COUNTROWS (FILTER (VALUES (Teams[Team]),SEARCH ( [Team], SELECTEDVALUE ( ProjectScore[Teams] ),, BLANK () )))

RETURN IF ( mycount > 0, 1 )

If I use this as a visual level filter where value =1 , this works for visuals where i have row context of team column
But I am unable to use it on a card Visula and even if I manage to do that, I think it will probably include few line items multiple times while calculating the sum (Ones which have more than one selected teams)