Forum Discussion
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
| ProjectID | WorkstreamID | Value |
| 59 | 125 | 2 |
| 98 | 6 | |
| 1 | 55 | 9 |
| 1 | 8 | |
| 1001 | 6 | |
| 98 | 5 |
In a Master Table, I have in a dataverse:
| ProjectID | WorkstreamID |
| 59 | 125 |
| 59 | 150 |
| 1 | 55 |
| 1 | 60 |
| 1 | 70 |
| 1001 | 250 |
| 98 | 10 |
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
Community 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.- Longhorns2013Regular 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.