Using LOOKUPVALUE with inactive relationship

[Anonymous]

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? 

[OwenAuger · Accepted solution]

Hi @Anonymous

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

[Anonymous]

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]

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