Getting row level context to DAX formula

[samlopez313]

Hi I have a pivot table that has a rows as Area and then SubArea. 

I'm creating a URL that has the Area on the link concatenated, but I want to use the subarea when needed, e.g. when a user expands the area and the row level context changes to the SubArea. Is there a way to do this? 
E.g. when the field is collapsed at the Area level the URL would be: https://contoso.apac.com/ (this I was able to do) but when the field is expanded to SubArea I want the URL to be: https://contoso.brunei.com/  (currently is just https://constoso.apac.com/ at this level too).

Is there a way to know at what level you are drilled down to? Then I could create an IF statement to select the correct column to contatenate. Or is there a way to use the value from the "Rows" of the pivot table to concatenate? 

Thanks.

[samlopez313]

Hi Alexis,

I'm trying the solution above but it just returns blank, making the URL like: https://contoso..com with no area or subarea, do you know why this might be? I'll add more details in a bit. It's like SELECTEDVALUE is not returning anything. 

[AlexisOlson]

What does your existing measure look like?

[samlopez313]

I'm creating this as a column and this is the formula: 

URL = 
VAR context = IF(ISINSCOPE(Table1[Area]),SELECTEDVALUE(Table1[Area]),SELECTEDVALUE(Table1[SubArea]))
RETURN
CONCATENATE(CONCATENATE("https://contoso.",context),".com")

 This is the current result:

As you can see the hyperlink is not concatenated correctly as it seems that "context" is empty. If I collapse the fields to the Area level it behaves the same. 

[AlexisOlson]

I meant the measure you had that just worked with just the Area. What does that look like?

Note that I wouldn't expect the expression I gave to work as a calculated column (since the scope is created by the visual). It has to be a measure.

[samlopez313]

Got it, yes! While you were responding I created the measure instead of the column and this is working great! Thanks! 

[AlexisOlson · Accepted solution]

This is a situation where ISINSCOPE is useful.

You could use it like this:

Context =
IF (
    ISINSCOPE ( Table1[SubArea] ),
    SELECTEDVALUE ( Table1[SubArea] ),
    SELECTEDVALUE ( Table1[Area] )
)