Search Filter For Hierarchy

[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 it obviously only works at the project level. Is there any way to have it use the Project ID / Workstream ID hierarchy from the master table to say that if the Workstream ID is blank then search the project and if I select a Workstream ID then locate the Workstream ID?

Measure 2 =

VAR searchvalue =

    SEARCH (

        SELECTEDVALUE ( Master[ProjectID] ),

        SELECTEDVALUE ( Data[ProjectID] ),

        ,

        BLANK ()

    )

RETURN

    IF ( searchvalue > 0, "Found" )

[v-yangliu-msft · Accepted solution]

Hi  @longhorn2009 ,

Here are the steps you can follow：

1. Create calculated column.

Flag =
var _select=SELECTCOLUMNS('Table',"1",[Project ID]
var _select1=
SELECTCOLUMNS(FILTER(ALL('Table'),'master table'[Project ID] in _select&&'Table'[Project ID]=EARLIER('master table'[Project ID])),"2",[Workstream ID])
return
IF(
    'master table'[Project ID] in _select && BLANK() in _select1 ,"Blank","Found")

2. Result:

Best Regards,

Liu Yang

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