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

USERELATIONSHIP to calculate SUM

Hello Community,

 

Hope you are well.

I have the below fact table which is connected to a dates table.

There are two relationships between the two tables:

  1. The active relationship is between 'Dates'[Date] and 'Fact'[Arrival Date]
  2. The inactive relationship is between 'Dates'[Date] and 'Fact'[Ship Date]

I have built 2 measures calculating the SUM of Q[Quantity],

  1. filtered on 'Fact'[Extraction Source] = "RM3" , and
  2. filtered on 'Fact'[Extraction Source] = "SM3"

, based on the active relationship.

 

I need help with writing the same measures based on the inactive relationship. I tried various measures by using USERELATIONSHIP , without any success.

 

Extraction SourceProductQuantityShip DateArrival Date
RM3PR1801 February 202106 April 2021
RM3PR28505 March 202112 April 2021
RM3PR34206 April 202109 June 2021
SM3PR11425 January 202126 March 2021
SM3PR2720 February 202102 April 2021
SM3PR34720 March 202102 June 2021

 

Thank you,

 

George

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@GeorgeGiannakis  while activating a relationship inside USERELATIONSHIP you need to explictly mention the filter direction; whatever is the first table inside USERELATIONSHIP that would filter the 2nd table inside parenthesis.

 

So,

 

ShipDate_RM3 =
CALCULATE(
SUM('Fact'[Quantity]),
USERELATIONSHIP(Dates[Date],'Fact'[Ship Date]),
FILTER('Fact','Fact'[Extraction Source] = "RM3")
)
 
instead of
 
ShipDate_RM3 =
CALCULATE(
SUM('Fact'[Quantity]),
USERELATIONSHIP('Fact'[Ship Date],Dates[Date]),
FILTER('Fact','Fact'[Extraction Source] = "RM3")
)

 

becuase Date filters Fact and not the other way round.

 

smpa01_0-1636728970585.png

 

smpa01_1-1636728989094.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

6 REPLIES 6
chat_peters
Helper I
Helper I

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 

 

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 in the visual in the picture should be 15, how do I achieve that? Can you please help me?datamodel.PNGVisual.PNG

smpa01
Super User
Super User

@GeorgeGiannakis  while activating a relationship inside USERELATIONSHIP you need to explictly mention the filter direction; whatever is the first table inside USERELATIONSHIP that would filter the 2nd table inside parenthesis.

 

So,

 

ShipDate_RM3 =
CALCULATE(
SUM('Fact'[Quantity]),
USERELATIONSHIP(Dates[Date],'Fact'[Ship Date]),
FILTER('Fact','Fact'[Extraction Source] = "RM3")
)
 
instead of
 
ShipDate_RM3 =
CALCULATE(
SUM('Fact'[Quantity]),
USERELATIONSHIP('Fact'[Ship Date],Dates[Date]),
FILTER('Fact','Fact'[Extraction Source] = "RM3")
)

 

becuase Date filters Fact and not the other way round.

 

smpa01_0-1636728970585.png

 

smpa01_1-1636728989094.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Hello @smpa01 ,

 

Thank you for your reply, I didn't know this detail.

 

I adjusted my measures but unfortunately, they continue to return blank.

 

What am I missing here?

 

 

ShipDate_RM3 =
CALCULATE(
SUM('Fact'[Quantity]),
USERELATIONSHIP('Dates'[Date],'Fact'[Ship Date]),
FILTER('Fact','Fact'[Extraction Source] = "RM3")
)

 

 

ShipDate_SM3 =
CALCULATE(
SUM('Fact'[Quantity]),
USERELATIONSHIP('Dates'[Date],'Fact'[Ship Date]),
FILTER('Fact','Fact'[Extraction Source] = "SM3")
)

 

GeorgeGiannakis_0-1636729647521.png

 

GeorgeGiannakis_1-1636729679712.png

 

Inactive relationship.

GeorgeGiannakis_2-1636729710849.png

 

Thank you,

 

George

 

 

 

@GeorgeGiannakis  I am not sure what is causing that still, please find my pbix





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


amitchandak
Super User
Super User

@GeorgeGiannakis , Try two measures like

 


calculate(sum(Fact[Quantity]), userelationship( 'Dates'[Date] ,'Fact'[Arrival Date]), filter('Fact', 'Fact'[Extraction Source] = "RM3"))


calculate(sum(Fact[Quantity]), userelationship( 'Dates'[Date] ,'Fact'[Arrival Date]), filter('Fact', 'Fact'[Ship Source] = "RM3"))



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Hello @amitchandak 

 

Thank you for your reply.

 

Please see my inputs below.

 

The measures ArrivalDate_RM3 and ArrivalDate_SM3 work correctly, as they rely on the active relationship.

 

ArrivalDate_RM3 =
CALCULATE (
SUM('Fact'[Quantity]),
FILTER (
'Fact','Fact'[Extraction Source] = "RM3")
)

 

ArrivalDate_SM3 =
CALCULATE (
SUM('Fact'[Quantity]),
FILTER (
'Fact','Fact'[Extraction Source] = "SM3")
)
 
 
The two below measures, ShipDate_RM3 and ShipDate_SM3 are based on the inactive relationship and return blank.
 
ShipDate_RM3 =
CALCULATE(
SUM('Fact'[Quantity]),
USERELATIONSHIP('Fact'[Ship Date],Dates[Date]),
FILTER('Fact','Fact'[Extraction Source] = "RM3")
)
 
ShipDate_SM3 =
CALCULATE(
SUM('Fact'[Quantity]),
USERELATIONSHIP('Fact'[Ship Date],Dates[Date]),
FILTER('Fact','Fact'[Extraction Source] = "SM3")
)
 
Can you please advise what I am doing wrong?
 
 Visuals
GeorgeGiannakis_1-1636727062573.png

 

 

Data Model

GeorgeGiannakis_0-1636726826228.png

 

Thank you

 

George

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors
Top Kudoed Authors