Show related items to set filter

[Aperszon]

Hello, 

I am having some trouble with a formula. 

Essentially what I am trying to achieve is this: my final report is filtered based on Phase ID, this will then show the Memo ID's linked to this phase. However 1 memoID has 1 to X relationship with PhaseId. So what I would then like to see is all the related phases to these memos.

For example I would select PhaseID 61. 

What I would then like to see as result is the related phases to above shown MemoID's. (below screenshot only for vizualisation)

MemoIDPhaseIDCount

1.0.1.11	1	2
1.0.1.11	7	1
1.0.1.11	8	1
1.0.1.11	9	1
1.0.1.11	15	2
1.0.1.11	65	1
1.0.14.20	31	1
1.0.1.15	32	1
1.0.14.20	32	2
1.0.1.11	56	1
1.0.1.15	60	1
1.0.14.20	60	2
1.A.18.17	60	3
1.A.18.1	60	4
1.A.12.8	61	1
1.A.12.7	61	2
1.B.18.7	61	3
1.B.18.2	61	4
1.0.14.20	61	5
1.0.1.15	61	6
1.A.18.1	61	7
1.0.1.11	61	8
1.0.1.11	63	2
1.A.18.1	63	4
1.0.14.20	63	10
1.0.1.15	63	11

Thank you in advance for any tips and tricks

[tamerj1]

Hi @Aperszon 
Sorry for the late reply. I hope this is what you're looking for.

Filter Measure = 
VAR CurrentMemo = SELECTEDVALUE ( Memo_Phase[MemoID] )
VAR SelectedPhase = SELECTEDVALUE ( Phase[ID] )
VAR SelectedMemos = CALCULATETABLE ( VALUES ( Memo_Phase[MemoID] ), ALL ( Memo_Phase ), Memo_Phase[PhaseID] = SelectedPhase )
RETURN
    IF ( CurrentMemo IN SelectedMemos, 1 )

[Aperszon]

@tamerj1 

Thank you very much, this is awesome 🙂  The measure is 90% there. 

once I add in the relationships to Phase and Memo the Measure fails. would you know what would affect this?

Before Relationship set

After added Relationship. 

[tamerj1 · Accepted solution]

Hi @Aperszon 
Apologies for the late resonse.
It has to be a disconnected table. If the table 'Phase" has to be connected with table 'Memo_Phase' then you need to create a seperate table for the slicer as follows

Slicer Table = VALUES ( Phase[ID] )

The adjust the filter measure as follows

Filter Measure = 
VAR CurrentMemo = SELECTEDVALUE ( Memo_Phase[MemoID] )
VAR SelectedPhase = SELECTEDVALUE ( 'Slicer Table'[ID] )
VAR SelectedMemos = CALCULATETABLE ( VALUES ( Memo_Phase[MemoID] ), ALL ( Memo_Phase ), Memo_Phase[PhaseID] = SelectedPhase )
RETURN
    IF ( CurrentMemo IN SelectedMemos, 1 )

[Aperszon]

This is amazing. thank you so much 🙂 

[tamerj1]

Hi @Aperszon 

how does the source data look like? Is it one table? What is [Count]? Is it a column or a measure?

[Aperszon]

@tamerj1 

The source data sits in 2 other tables.

see below link for an extract of the source data and how it is sructured

and count is currently a column in the table, used for structuring my source data to the correct location. not necessarily needed for this retrieval (I think).

https://docs.google.com/spreadsheets/d/1CL8DFNMPORLDx7BG2u61z4_8PwxjLbXO/edit?usp=sharing&ouid=10955...