Multi-Select Search Measure

[longhorn2009]

HI All,

I have been playing out with how to figure out how to do a contains filter for two tables that I am unable to join due to the lack of data for some of the values.

In on table I have:

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

In a master table I have in the dataverse, I have:

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

Since some of the data entered don't have Project IDs, I would like to filter the data from the Project Level to show all underlying workstreams (including blank IDs). If there is a Workstream ID, when I select the second level of the hierarchy from the master table, I would like to only see that Workstream ID.

Normally I would use a key to join the two tables but the items with blank Workstream IDs would not be brought in. 

I tried the following measure but as soon as you select mulitple Project IDs, it reverts to showing all matches and not just those in the selected Project ID Master Filter. It does work when you only select one Project ID or one Workstream ID, however users are asking for this to work with a Multi-select filter:

Filter =

VAR searchproject =

EXACT(

        SELECTEDVALUE ( Workstream[Project ID] ),

        SELECTEDVALUE ( ADKARStatusChecks[Project ID] )

    )

VAR searchworkstream =

EXACT(

        SELECTEDVALUE ( Workstream[Workstream ID] ),

        SELECTEDVALUE ( ADKARStatusChecks[Workstream ID] )

    )

RETURN

IF( HASONEVALUE(Workstream[Project ID] ) && searchproject=True, "Found",

IF( NOT HASONEVALUE(Workstream[Project ID] ) && searchproject=False, "Found"))

Any help will be greatly appreciated!

[Ashish_Mathur]

Hi,

Clearly show the expected result.

[v-yueyunzh-msft]

Hi , @longhorn2009 

According to your description, you want to "do a contains filter for two tables"  and thanks for your sample data.

I am not fully understand the end result you want to need, and in Table2 ,like [Project ID]=59 , it has three [Workstream ID]. What do you want to need?

And what is the field in the slicer? Can you share the end result sample data to us, like a table ?

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[longhorn2009]

So in the example above with the fake data, I would be able to select Project ID 59 and 1 from the Master Table filter and get the value of 18.

[v-yueyunzh-msft]

Hi , @longhorn2009 

I still not understand your need , when we select Project ID 59 and 1 from the Master Table filter, it just return the value of 18 , like this:

If this method does not meet your needs, you can provide us with  the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly