Forum Discussion

Longhorns2013's avatar
Longhorns2013
Regular Visitor
3 years ago

Search Two Tables Based On Multiple Criteria

Hi All,

 

I have been stuck on this for awhile and any help would be appreciated:

 

On one table I have 

ProjectIDWorkstreamIDValue
591252
98 6
1559
1 8
1001 6
98 5

 

In a Master Table, I have in a dataverse:

ProjectIDWorkstreamID
59125
59150
155
160
170
1001250
9810

 

I have used the following calculation to try and get a search match, which works when selecting one value but as soon as you select more than one from the dropdown, it reverts to everything that is in both tables which is what I do not want to occur.

 

 

 

 

Filter = 
VAR searchproject =
EXACT(
        SELECTEDVALUE ( Master[Project ID] ),
        SELECTEDVALUE ( Values[Project ID] )
    )


VAR searchworkstream =
EXACT(
        SELECTEDVALUE ( Master[Workstream ID] ),
        SELECTEDVALUE ( Values[Workstream ID] )
    )

RETURN
If(HASONEVALUE(Workstream[Workstream ID]) && searchworkstream>0, "Found",
If(NOT HASONEVALUE(Workstream[Workstream ID]) && searchproject>0,"Found"))

 

 

 

 

Any idea how I can get the following example:

Select Project 1001 and 98 from a dropdown filter connected to the Master Table and then show a value of 11?

 

Note I cant do a simple join because some users aren't inputting their information at the workstream level.

 

Any help is greatly appreciated!

2 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support

    Hi, Longhorns2013 ;

    When we select Project 1001 and 98 from a dropdown filter , the value why is 11 rather than 17(6+6+5)?

    can you provide the logic about those result and your Workstream table?


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Longhorns2013's avatar
      Longhorns2013
      Regular Visitor

      You are correct! Sorry about that, I missed the first 98 in my example. 

      So if I select a project, in theory I want everything related to that project and if I select a project and associated workstream, I just want that specific workstream.