Forum Discussion
Userelationship measure and count rows
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 😞 Link to sample file: https://www.dropbox.com/s/8is8eo29lot8q8i/example.pbix?dl=0
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
Total Orders = COUNT('Order Fact'[Order ID])
I created two columns using look up to get caller location and buyer location into the fact table. I need to create a measure or a flag so that my visual doesn't look confusing. For example the total number of rows with the location filter set to Guadalajara in the visual in the picture should be 14, how do I achieve that? Can you please help me?
6 Replies
You're on the right track. You need a measure that returns 1 when location is in eithe caller or buyer. The bit I think you're missing is that you can then put the measure into the visual level filter for that visual and set to "is 1". (filter bar on right).
In terms of writing the measure afraid I'm afway from a computer but could send something on Sunday evening.
bcdobbs thank you for replying. I have tried that but I get a circular reference when I do that 😞 I need something that uses either the filter function or calculatetable. I just don't know how to get there. Let me figure out a way to attach my example power bi file
If you upload to one drive or drop box etc and share a link then as soon as I get back to a computer I'm sure we can sort.
Here is the link to the dropbox file
Hi chat_peters ,
Thank you very much for your feedback!
If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly.
Best Regards,
Henry
