Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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:
I have built 2 measures calculating the SUM of Q[Quantity],
, 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
Solved! Go to Solution.
@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,
becuase Date filters Fact and not the other way round.
Proud to be a Super User!
New Animated Dashboard: Sales Calendar
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?
@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,
becuase Date filters Fact and not the other way round.
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?
Inactive relationship.
Thank you,
George
@Anonymous I am not sure what is causing that still, please find my pbix
Proud to be a Super User!
New Animated Dashboard: Sales Calendar
@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"))
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.
Data Model
Thank you
George
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!