Hierachy and disconnected table question

[MoMoZA]

I will try to keep this as brief and summarized as possible.

I have 2 tables that are disconnected.

The first table is one that purely has columns which define a hierarchy. 

Area	Business Unit	Portfolio	Subportfolio
Area 1	BU1	PO1	SP1
Area 1	BU1	PO2	SP2
Area 1	BU2	PO3	SP3
Area 1	BU2	PO4	SP4

From the table above I create a hierarchy and hide the actual columns in Reoprt View.

A simplified version of the second table is like this:

Date	Level	Result
2020-03-31	Area 1	1,000,000
2020-03-31	BU1	500,000
2020-03-31	BU2	800,000
2020-03-31	PO1	300,000
2020-03-31	PO2	400,000
2020-03-31	SP1	200,000
2020-03-31	SP2	200,000

Don't worry about the numbers for now.

In a Matrix visual, I want to be able to drop the hierachy on the rows (obviously with drilldown enabled) and have a DAX measure in the Values section of the matrix that will look up the correct value for each level of the hierarchy, no matter how far down the user drills.

I'm having trouble with creating this measure.

Can anyone assist?

[Greg_Deckler]

Can you post what you have in terms of a measure calculation? You definitely need to be using ISINSCOPE. https://docs.microsoft.com/en-us/dax/isinscope-function-dax

[MoMoZA]

Yeah I figured as much that ISINSCOPE is the key but I can't wrap my head around how to get it into a measure.

At the moment, I've created 4 measures, each one doing an "ISINSCOPE" for each level of the hierachy:

IsInscope Area = ISINSCOPE( 'Hierarchy)'[Area] )

IsInscope Business Unit = ISINSCOPE( 'Hierarchy)'[Business Unit] )

IsInscope Portfolio = ISINSCOPE( 'Hierarchy)'[Portfolio] )

IsInscope Subportfolio = ISINSCOPE( 'Hierarchy)'[Subportfolio] )

Now I need a master measure that refers to these.

I know I'm close but just can't think of how.

[Greg_Deckler]

Generally,

Measure =
  SWITCH(TRUE(),
    ISINSCOPE('Hierarchy'[Area]),SUMX(FILTER('Table','Table'[Level]=MAX('Hierarchy'[Area]),[Result]),
    ISINSCOPE(...)
  )