Forum Discussion

Longhorns2013's avatar
Longhorns2013
Regular Visitor
3 years ago
Solved

Lookup or Search Multi-Select Between Tables

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 IDWorkstream IDValue
59 2
98 6
1 9
15596
17161
1001 9

 

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

Project IDWorkstream ID
594
595
596
987
988
989
1532
1559
1716
1720
1001254

 

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.
Need Help
  • sturlaws's avatar
    sturlaws
    3 years ago

    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.

     

5 Replies

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    3 years ago

    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's avatar
      Longhorns2013
      Regular Visitor
      3 years ago

      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's avatar
        sturlaws
        Resident Rockstar
        3 years ago

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