cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Anonymous
Not applicable

## 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 Source Product Quantity Ship Date Arrival Date RM3 PR1 8 01 February 2021 06 April 2021 RM3 PR2 85 05 March 2021 12 April 2021 RM3 PR3 42 06 April 2021 09 June 2021 SM3 PR1 14 25 January 2021 26 March 2021 SM3 PR2 7 20 February 2021 02 April 2021 SM3 PR3 47 20 March 2021 02 June 2021

Thank you,

George

1 ACCEPTED SOLUTION
Super User

@Anonymous  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")
)

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.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
6 REPLIES 6
Helper III

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?

Super User

@Anonymous  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")
)

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.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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")
)

Inactive relationship.

Thank you,

George

Super User

@Anonymous  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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Super User

@Anonymous , 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"))

Anonymous
Not applicable

Hello @amitchandak

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

Data Model

Thank you

George

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors