Join PowerBI tables based on "where contains TEXT"

Question

I'm not sure this can be done, but the data is all there so I'm hoping so!

In our projects we have a lookup field to select which delivery groups (DGs) are interested in working on the project. There could be 1 DG or 20 interested, its unlimited. Instead of having a line per DG per project, our database includes them in a field which links them together like 'DG1; DG2; DG3'

PROJECT   DELIVERY GROUPS KEY
A         55171; 55172; 55173
B         55173
C         55170; 55171;

I then have a table to show each distinct delivery group and its key

DG KEY      DG NAME
55170       DG0
55171       DG1
55172       DG2
55173       DG3

So in PowerBI I want to be able to have the Delivery Groups table in a filter so the user can select one or many of them, and then it only shows projects where that delivery group/s is specified

FILTER = 55171

PROJECT   DELIVERY GROUPS KEY  INCLUDE?
A         55171; 55172; 55173  Y
B         55173                N
C         55170; 55171;        Y

But I don't know how to link them in the relationship diagram so its 1:many (1 being the distinct delivery groups table DG key, many being the delivery group key in the project table) The relationship diagram join would have to be something like "JOIN WHERE 'Project table'[Delivery Group Key] CONTAINS 'Delivery Group table'[Delivery Group Key]

The workaround at the moment is just typing what I'm looking for in the filters using "contain" for the Project delivery groups, but we have other tables which use delivery groups so I'd rather the user just had to click once and everything was filtered

All the tables are SQL based fed into Jaspersoft Studio to create Excel files (we can't connect PowerBI direct to our data yet so has to be extracted as excel, saved to SharePoint and PowerBI runs the data from there) if that helps any solutions at the source instead

Any help greatly appreciated!!

Answer 1

You can't create custom relationships in your model like that.

I'd suggest creating a column on your delivery group table that looks up what project it's associated with and then creating a 1-to-many relationship from Projects[Project] to DG[CalculatedCol]. This should be equivalent to what you are wanting assuming a single delivery group isn't associated with multiple projects.