cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
TobiasOwen Member
Member

Using LOOKUPVALUE with inactive relationship

Hi,

 

I have an issue with LOOKUPVALUE returning values incorrectly.

 

I have two dimension tables in a snowflake scheme. One is 'Personnel' that contains data for 'current age' and 'age on leaving'. The other is an 'Age group' table that buckets those ages into groups, e.g. 30-34, 35-39, etc.

 

There is an active relationship between the two tables between 'Personnel'[Current Age] and 'Age Groups'[Age], as this is the most commonly-used query (i.e. what age group is a member of staff currently in?).

 

However, I want to find the age group that a person was in when they left. I am trying to create this as a calculated column in the 'Personnel' table using LOOKUPVALUE, with the following formula:

Age Group On Leaving = LOOKUPVALUE('Age Group'[Age Group],'Age Group'[Age],Personnel[Age On Leaving])

However, this is not responding as expected. I am only getting results for entries where the 'current age' matches the 'age on leaving'. All other entries are returning blank results.

 

I was advised that LOOKUPVALUE can be used irrespective of relationships between two tables, but it seems as though the active relationship in my model is impacting this calculated column somehow.

 

How can I get the formula to return values for ALL entries in the 'age on leaving' column? 

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: Using LOOKUPVALUE with inactive relationship

Hi @TobiasOwen

 

Interesting, I can reproduce your issue if the relationship between Personnel and 'Age Group' has Cross Filter Direction set to "Both".

 

Is that how the relationship is set up in your model?

 

It would seem the options are:

  1. Change the Cross Filter Direction to Single (unless there is some reason it needs to be Both)
  2. Write the calculated column as something like:
    Age Group On Leaving = 
    VAR AgeOnLeaving = Personnel[Age On Leaving]
    RETURN
        CALCULATE (
            SELECTEDVALUE ( 'Age Group'[Age Group] ),
            ALL ( Personnel ),
            'Age Group'[Age] = AgeOnLeaving
        )

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
OwenAuger Super Contributor
Super Contributor

Re: Using LOOKUPVALUE with inactive relationship

Hi @TobiasOwen

 

Interesting, I can reproduce your issue if the relationship between Personnel and 'Age Group' has Cross Filter Direction set to "Both".

 

Is that how the relationship is set up in your model?

 

It would seem the options are:

  1. Change the Cross Filter Direction to Single (unless there is some reason it needs to be Both)
  2. Write the calculated column as something like:
    Age Group On Leaving = 
    VAR AgeOnLeaving = Personnel[Age On Leaving]
    RETURN
        CALCULATE (
            SELECTEDVALUE ( 'Age Group'[Age Group] ),
            ALL ( Personnel ),
            'Age Group'[Age] = AgeOnLeaving
        )

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

TobiasOwen Member
Member

Re: Using LOOKUPVALUE with inactive relationship

Hi Owen, you're right, the relationship is set to 'Both'. This setup is required due to cross-filtering between different 'branches' of the snowflake schema.

 

I have therefore tried your alternative solution, and it works like a charm, thank you!

 

For my own understanding, could you please explain what's happening in that formula to produce the desired result? Thanks very much.

OwenAuger Super Contributor
Super Contributor

Re: Using LOOKUPVALUE with inactive relationship

Glad it worked!

 

I'll just restate the formula with some colour-coding:

Age Group On Leaving = 
VAR AgeOnLeaving = Personnel[Age On Leaving]
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Age Group'[Age Group] ),
        ALL ( Personnel ),
        'Age Group'[Age] = AgeOnLeaving
    )

The short description is that the current row's value of Age On Leaving is applied as a filter on 'Age Group'[Age], then the resulting value of Age Group is grabbed.

 

The steps to do this are (colour-coded):

  1. First, store Age On Leaving from the current row in a variable called AgeOnLeaving. It is not essential to use a variable, but I did so for readability.
  2. Within CALCULATE, apply a filter on the 'Age Group'[Age] column equal to AgeOnLeaving.
  3. Since CALCULATE converts the row context in which it is called (the current row of Personnel) into an equivalent filter ("context transition"), we want to clear this with ALL ( Personnel ), since the only filter we want is from step 2
  4. Having applied these filters, get the single value of Age Group using SELECTEDVALUE. If there were somehow multiple values of Age Group, blank would be returned.

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors