Forum Discussion
How to Use multiple selected values and contains string together
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
Super UserHi 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
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)