Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

To filter aux table values with the fact table values

I have a request that to show only the values in the filter which can be found in my fact table, in a nutshell: My problem is that I can't use bi-directional relationships between the tables...
Need Help
  • sevenhills's avatar
    sevenhills
    3 years ago

    I am not sure userelationship works in your way of explanation. Assuming you dont want to create relationships and want the text value from another table.

    note: Relationships - active, inactive are always preferred approach. 

     

    Calculated column approach:

     

    Name Column = LOOKUPVALUE(P123_AuxTable[Name], P123_AuxTable[ID], P123_FactTable[ID])

     

    Measure approach:

     

    Name Measure = 
var _selVal = SELECTEDVALUE(P123_FactTable[ID])
var _name = calculate(Max(P123_AuxTable[Name]), P123_AuxTable[ID] = TREATAS( { _selVal }, P123_AuxTable[ID]))

return _name

     

     

    Data:

    FYI: I added ID "99" to check the outlier. 

    Output:

    Hope this helps!

     

sevenhills's avatar
sevenhills
Icon for Super User rankSuper User
3 years ago

I am not sure userelationship works in your way of explanation. Assuming you dont want to create relationships and want the text value from another table.

note: Relationships - active, inactive are always preferred approach. 

 

Calculated column approach:

 

Name Column = LOOKUPVALUE(P123_AuxTable[Name], P123_AuxTable[ID], P123_FactTable[ID])

 

Measure approach:

 

Name Measure = 
var _selVal = SELECTEDVALUE(P123_FactTable[ID])
var _name = calculate(Max(P123_AuxTable[Name]), P123_AuxTable[ID] = TREATAS( { _selVal }, P123_AuxTable[ID]))

return _name

 

 

Data:

FYI: I added ID "99" to check the outlier. 

Output:

Hope this helps!