One Slicer to respond to two different columns in the same fact table

[chat_peters]

Hello, I have a question about userelationship function, I've been tasked with having one filter respond to two different columns in the same table 😞
I have four tables in my model. Orderfact, CallerDim, BuyerDim and People Dim. The ask is to be able to pick a location from people fact and have it respond to both columns in order fact. For example say if
order ID 1 - Caller is from Lima and the Buyer is from Guadalajara
order ID 2 - the Caller is from Guadalajara and the buyer is from Leon
Order ID 3 - Caller and Buyer are both from Guadalajara. When I choose Guadalajara from the location filter it should be able to pick up all three of the records. 

Currently I can achieve this by creating a measure with userelationship,

inactiverelationshipmeasure = CALCULATE('Order Fact'[Total Orders],USERELATIONSHIP('Order Fact'[BuyerID],'PeopleDim'[Id]))

However, in order for the filter to respond to both columns in the same table, I need to drag this measure and Total Orders measure on to the canvas

Total Orders = COUNT('Order Fact'[Order ID])

I need to create a measure or a flag so that my visual doesn't look confusing. For example the total number of rows in the visual in the picture should be 14, how do I achieve that? Can you please help me? I've tried filter, calculatetable, nothing seems to give me the count of 14 I need. I need a count where either caller is from the selected loation or the buyer is from the selected location or both are from the selected location. Can someone please help me?

[johnt75 · Accepted solution]

You could try

Total Orders =
var chosenLocations = VALUES( 'PeopleDim'[Location])
return CALCULATE( SUMX('Order Fact'), IF( RELATED( 'CallerDim'[Location]) IN chosenLocations ||
    RELATED( 'BuyerDim'[Location]) IN chosenLocations,
1),
REMOVEFILTERS('PeopleDim'[Location])
)

[chat_peters]

This works perfect. I was agonizing over measure created with userelationship. Now I have to go back and do more research on removefilters! Thank you so so so much 😊

[johnt75 · Accepted solution]

You could try

Total Orders =
var chosenLocations = VALUES( 'PeopleDim'[Location])
return CALCULATE( SUMX('Order Fact'), IF( RELATED( 'CallerDim'[Location]) IN chosenLocations ||
    RELATED( 'BuyerDim'[Location]) IN chosenLocations,
1),
REMOVEFILTERS('PeopleDim'[Location])
)

[chat_peters]

@johnt75 The solution you provided would have worked wonderful if I was not on direct query mode. I am not allowed to do in. Also I built this solution in power bi, but in reality the model is in SSAS, direct query mode. So I can't use lookup to get the location to populate in the buyer table. I can try related, however, userelationship only works inside of calculate. How can I go about populating the location in buyer dim using userelationship? Also is there a work around to using IN? thank you in adavance for taking your time to read this post.

[johnt75 · Accepted solution]

Not sure if this will work but you can try

Total Orders =
var buyerOrders = CALCULATETABLE( VALUES('Order Fact'[Order ID]), 
   USERELATIONSHIP( 'Order Fact'[BuyerID], 'PeopleDim'[Id])
)
var callerOrders = CALCULATETABLE( VALUES('Order Fact'[Order ID]), 
   USERELATIONSHIP( 'Order Fact'[CallerID], 'PeopleDim'[Id])
)
var allOrders = DISTINCT( UNION( buyerOrders, callerOrders))
return CALCULATE( SUMX('Order Fact', 1), REMOVEFILTERS( 'PeopleDim'), 
   TREATAS( allOrders, 'Order Fact'[Order ID])
)

[chat_peters]

Brilliant! I've been using the filter function in all different ways. You've encouraged me to learn more about variables. Thank you so much. It works in my power bi. I hope it works in SSAS tabular model🤞

[amitchandak]

@chat_peters , You can use an independent table and filter both

calculate([Total Orders] filter('Order Fact','Order Fact'[BuyerID] in values('PeopleDim'[Id]) || 'Order Fact'[CallerID] in values('PeopleDim'[Id]) ))

[chat_peters]

@amitchandak Thank you for taking your time to respond. Unfortunately the measure only looks at records where the caller is from the selected location or the caller and buyer are both from the selected location. Thank you for pointing me in a different direction, I am going to try a few things with your measure 🙂