Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

How to force or make a measure to use the relationship in DAX

Hi all,

 

I have two "Many to One" relationships in the Tabular Model to Dates table where Table1 is a Fact table and Dates is a dimensin table as mentioned below:

'Table1'[Date Key] --> 'Dates'[Date Key]

'Table1'[ReceiptDateKey] --> 'Dates'[Date Key]

 

I have a measure X which is SUM ( 'Table1'[Sale] ), I wanted to force or make this measure X to use the relationship 'Table1'[ReceiptDateKey] --> 'Dates'[Date Key] in the DAX calculations.

How I can achieve this, please can you help with DAX Command.

 

Regards,

 

Ranjan.

2 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

Please correct me if I wrongly understood your question.

I assume you have active relationship: 'Table1'[Date Key] --> 'Dates'[Date Key]

                              inactive relationship: 'Table1'[ReceiptDateKey] --> 'Dates'[Date Key]

 

Please try the below.

 

Measure X = calculate ( SUM ( 'Table1'[Sale] ), userelationship ( 'Table1'[ReceiptDateKey] , 'Dates'[Date Key] ))

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

Anonymous
Not applicable

Thank you @Jihwan_Kim for the information. I will give it a go and update if it works fine.

 

Regards,

 

Ranjan.

View solution in original post

5 REPLIES 5
Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

Please correct me if I wrongly understood your question.

I assume you have active relationship: 'Table1'[Date Key] --> 'Dates'[Date Key]

                              inactive relationship: 'Table1'[ReceiptDateKey] --> 'Dates'[Date Key]

 

Please try the below.

 

Measure X = calculate ( SUM ( 'Table1'[Sale] ), userelationship ( 'Table1'[ReceiptDateKey] , 'Dates'[Date Key] ))

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Anonymous
Not applicable

Thank you @Jihwan_Kim 

To be honest, I will be creating the second relationship 'Table1'[ReceiptDateKey] --> 'Dates'[Date Key] and first relationship already exist in the model - 'Table1'[Date Key] --> 'Dates'[Date Key]

I don't know how to verify active and inactive relationships in the model, is there a way to check this or we need to assign active or inactive in the relationships/measure somewhere?

 

Regards,

 

Ranjan

Hi, @Anonymous 

If you have only one relationship between two tables, the connected line on the Data Model Page will show a line (active).

If you create a second relationship between the same-two tables, that already have the first relationship, the second connection line will show a dotted line (inactive).

You can control which one shows the active line and which one shows the inactive line.

 

Thank you.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Anonymous
Not applicable

Thank you @Jihwan_Kim for the information. I will give it a go and update if it works fine.

 

Regards,

 

Ranjan.

Anonymous
Not applicable

Thanks @Jihwan_Kim for the DAX command and it works fine.

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.