measure filter for pivot table

[michaelbeer]

Hi, 

I'm getting crazy, can't find a solution for my problem so far: 

I'm using the Power BI build-in "text-search" visual to search for a certain Project-ID inside my table of projects. 

The table is grouped by project-cluster-IDs which have several project-IDs assigned. (It's a 1:n cardinality)

The result of the search I would like to show with two different visuals: 

1) a table, which shows the selected row of the Project-ID (filtered by the "text-search"-visual) -> works fine

2) a matrix (pivot), which shows the project-cluster-ID with all project-IDs rows related (linked to the selected project-ID). 

So I have to "store" somehow the global selection of the project-cluster-ID and then check every line of
my table if it is equal to the selected value of project-cluster-ID. I know there must be something with outer and inner context.    
The measure filter I wanted to assign to my table as a "measure" filter, to filter the "1" and "0" which fit to the global selected project cluster-ID.

My ideas:
[Assign selected Cluster ID] = ??? (SELECTEDVALUE('my_table[Project Cluster ID])

Measure filter =
Var __Selected_Cluster_ID = [Assign selected Cluster ID]
Return
IF(
MAX('my_table'[Project Cluster ID]) == __Selected_Cluster_ID,
1,
0
)

[PaulPalma]

@michaelbeer 

Not knowing your data model, here is an idea:

- Create a Measure Table for the desired Pivot and add a relationship on ClusterIDs.

- If this is an isolated case you might get away with bi-directional relationship for a cluster to projects relationship.

- if you don't have a cluster dim table you can have a single direction relationship between the Projects and MeasureTable-Pivot.

Hope this gives you some ideas.

Paul