Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Search Filter For Hierarchy

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

 

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

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

 

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" )
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  Anonymous ,

     

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  Anonymous ,

     

    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