cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mpataki32
Helper III
Helper III

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:

mpataki32_0-1671031672894.png


My problem is that I can't use bi-directional relationships between the tables (in other words: I can't connect them on the modelling pane). 

So I would like to create a DAX measure with USERELATIONSHIP which will return for my slicer the values which can be found in the fact table, but "translated" it with the aux table's Name field. 

1 ACCEPTED SOLUTION
sevenhills
Super User
Super User

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  

 

sevenhills_2-1671042811762.png

 

Data:

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

sevenhills_0-1671042645720.png

Output:

sevenhills_1-1671042690483.png

Hope this helps!

 

View solution in original post

1 REPLY 1
sevenhills
Super User
Super User

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  

 

sevenhills_2-1671042811762.png

 

Data:

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

sevenhills_0-1671042645720.png

Output:

sevenhills_1-1671042690483.png

Hope this helps!

 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors