Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 leavi...
Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    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

OwenAuger's avatar
OwenAuger
Super User
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    OwenAuger
    Super User
    8 years ago

    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