Forum Discussion
Getting row level context to DAX formula
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.
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] ) )
6 Replies
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] ) )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.
What does your existing measure look like?
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.
