DAX UseRelationship

[nchamilton162]

Can someone please help me understand if I'm using the proper dax function to achieve the following?

I have a factSales table and a Date Dim table. The fact table has OrderDate and ShipDate. I have an active relationship between OrderDate from my fact table and Date from my Date Dim table. I have an inactive relationship between ShipDate on my facttable and Date on my Date Dim table. This works fine

CALCULATE([Total Sales by Ship Date],USERELATIONSHIP('Dim Date'[Date],FactSales[ShipDate])). I don't necessarily always want to use an aggregate though. 
What I would like to do is return something like Day of Week for my ShipDate from the Date Dim table. Is the USERELATIONSHIP the proper approach in this case? 



Thank you

[Ashish_Mathur]

Hi,

In the Calendar table, via the FORMAT() function, extract the day of the week.  Try this measure then

Measure = calculate(max('Dim Date'[Day of week]),USERELATIONSHIP('Dim Date'[Date],FactSales[ShipDate]))

[parry2k]

 @Anonymous I don't think you understood the question from the OP, and becuase of that your answer is not relevent. 

@nchamilton162 check this video on my YT channel and I hope it helps. RELATED DAX function with inactive relationships in Power BI

[Anonymous]

Hi @nchamilton162 ,

Of course you can, but there is already an active relationship between OrderDate from fact table and Date from Date Dim table, so there is no need to create another one-to-many relationship for the Date from Date Dim table. 

In addition to USERELATIONSHIP, you can also use RELATED to get Day of Week. Create custom column in fact table with below DAX.

Column = RELATED('Dim Date'[Day of Week])

——————————————————————————————————————————————————

If my answer helps you solve the problem, please accept my answer as a solution and let it be seen by more people in need.

Best regards,

Mengmeng Li