Lookup or Search Multi-Select Between Tables

[Longhorns2013]

Hi All,

I have been stuck on this for over a week now and could really use some help:

I have one data table that contains the following information:

Project ID	Workstream ID	Value
59		2
98		6
1		9
1	559	6
1	716	1
1001		9

In another Master Project/Workstream table, I have the following values:

Project ID	Workstream ID
59	4
59	5
59	6
98	7
98	8
98	9
1	532
1	559
1	716
1	720
1001	254

I am unable to do a proper join because some users are entering their values without an appropriate workstream so I am trying to search if the values (could be multi-select) are contained in values table and if so, provide me the sum of their values. For example, if I select 1 and 98 from the master list, I would like to see a value of 22.

I have tried the following calculation but it keeps pulling in all of the numbers if I select 1 and 98 for example rather than just the two I selected. It does work for single select but the users are requesting the ability to select multiple:

Filter =

VAR searchproject =

    SEARCH (

        SELECTEDVALUE ( Master[Project ID] ),

        SELECTEDVALUE ( Values[Project ID] ),

        ,

        BLANK ()

    )

VAR searchworkstream =

    If(EXACT(

        SELECTEDVALUE ( Master[Workstream ID] ),

        SELECTEDVALUE ( Values[Workstream ID] )

    )=True,1,0)

RETURN

If(HASONEVALUE(Master[Workstream ID]) && searchworkstream>0, "Found",

If(NOT HASONEVALUE(Master[Project ID]) && searchproject>0,"Found"))

Then my values calculation is 

CALCULATE(SUM(Value),FILTER(Values,[Filter]="Found"))

Is there any way to do this but include a multiple selection from the Master Project ID Dropdown? Any help as soon as possible is very, very much appreciated.

[sturlaws · Accepted solution]

Hi, @Longhorns2013,

If you drop the relationship between your two tables, and write your measure like this:

Measure =
VAR _master =
    CALCULATETABLE ( VALUES ( Master[Project ID] ) )
RETURN
    SUMX (
        CALCULATETABLE (
            Project,
            FILTER (
                Project,
                CONTAINS ( _master, Master[Project ID], Project[Project ID] )
            )
        ),
        Project[Value]
    )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Longhorns2013]

Hi @sturlaws - This is great thank you! How would I add the workstream level search into that same calculation so it does a contains for exact project level IDs first and then exact workstream?

[sturlaws]

Depends on how you want to handle lines with missing workstream id

[Longhorns2013]

@sturlaws - if the Workstream ID is missing, it should just show the project ID associated to it. If it isnt then I would want the Workstream data.

[sturlaws]

have a look at the attached pbix-file below, I replaced null with Workflow ID is missing to improve usability