topic Re: Show related items to set filter in DAX Commands and Tips

Show related items to set filter

Aperszon — Mon, 19 Sep 2022 13:51:54 GMT

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

Re: Show related items to set filter

tamerj1 — Mon, 19 Sep 2022 14:45:35 GMT

Hi Aperszon

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

Re: Show related items to set filter

Aperszon — Tue, 20 Sep 2022 07:37:00 GMT

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=109555599787747575679&rtpof=true&sd=true

Re: Show related items to set filter

tamerj1 — Tue, 20 Sep 2022 13:47:22 GMT

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 )

Re: Show related items to set filter

Aperszon — Wed, 21 Sep 2022 07:53:48 GMT

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.

Re: Show related items to set filter

tamerj1 — Wed, 21 Sep 2022 13:16:52 GMT

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 )

Re: Show related items to set filter

Aperszon — Wed, 21 Sep 2022 14:27:27 GMT

This is amazing. thank you so much 🙂