Dynamic/Complex relationships - Aggregated output

[mywidow]

Hi all,

I have these two tables:

Summary
ID	RESULT
2015	100
2016	80
2017	90
2018	110

Detail
ID	TITLE	AREA	VALUE
2015	A	NORTH	1
2016	B	NORTH	2
2016	C	WEST	3
2017	D	WEST	4
2017	E	EAST	5
2018	F	SOUTH	6

and I'd like to have a view like this:

but with the following behaviour:

when I click on a column in the "SUMMARY" view (eg. 2016):

• "DETAIL v1" shows all the columns with equal or lower ID (eg. 2015 and 2016 are highlighted)
• "DETAIL v2" shows the result based on the accumulated output (eg. 2015+2016 --> NORTH=3, WEST=3)

I'm new to Power BI, so here's what I thought I should do (but didn't succeed):

1. define a custom relationship: Summary[ID]>=Detail[ID]. I didn't find a way to do it.
2. remove the Summary[ID]=Detail[ID] relation and define the following measures*
• M_GET_SELECTION = IF(HASONEVALUE(Summary[ID]); SELECTEDVALUE(Summary[ID]); 9999)
• M_MY_FILTER = IF(HASONEVALUE(Summary[ID]);IF(MAX(Detail[ID])<=Detail[M_GET_SELECTION]; "Y"; "N"); "Y")

* for some reasons the measure M_MY_FILTER shows the values I expect (see the following image: "SUMMARY" ha 2016 selected, and "DETAIL v3" has "M_MY_FILTER"=Y where ID<=2016).

Now:

• if I add the filter "M_MY_FILTER contains Y" to "DETAIL v3" it works --> if "SUMMARY[2016]" is highlighted "DETAIL v3" only shows the first 3 rows
• if I add the same filter to "DETAIL v1" or "DETAIL v2" nothing happens.

Thank you in advance.

[mywidow · Accepted solution]

Nevermind, I solved it on my own.

If anybody is interested, "CROSSJOIN + calculated column + filter" do the trick.

[v-frfei-msft]

Hi @mywidow,

Kindly mark your answer as a solution to close the case please. Thanks in advance.

Regards,

Frank