3 or more USERELATIONSHIPS functions in 1 measure

[michael_knight]

Hi,

I'm having some issues with a measure I'm trying to write. Due to how our system is built, this is the way that I need to design/write our measures

This is a replica of the data for testing purposes. The relationships in this are exactly what they're like in the main dataset

I am using the USERELATIONSHIP function with the date because I am creating multiple measures with different date so I'm unable to create an active relationship on that front

With the Sales Owner and Property Owner fields, I have to use the USERELATIONSHIP function as the making the relationships active will break the report 

This is the measure that I created below for this example

Count Rows Test = CALCULATE(COUNTROWS('Sheet1 - Help'),
USERELATIONSHIP('Sheet1 - Help'[Date], 'Date'[Full Date]),
USERELATIONSHIP('Sheet1 - Help'[Sales Owner], 'Sheet2 - Help'[Sales Owner]),
USERELATIONSHIP('Sheet1 - Help'[Property Owner],'Sheet2 - Help'[Property Owner]))

I have all of the relationships set to inactive and I am using the USERELATIONSHIP to make them active in this measure. The issue I'm having is that I can use 2 USERELATIONSHIP functions in 1 measure but it won't let me do any more than that. I get this error message

 This is the details with the error]

I'll add the PBIX file below

https://www.dropbox.com/s/56ejgy9f9kv98kd/Help%2023-10-2020.pbix?dl=0

Cheers,

Mike

[michael_knight]

Hi,

Still really struggling with this. Any suggestion will be greatful

Cheers

[danno]

Hi Mike, 

can you tell me what your sheets contain as it looks like they have exactly the same data. What purpose do the separate sheets have?  

I would suggest that you need to look at how your data is modeled, and perhaps merge / append the two sheets in PowerQuery before using them in your model. 

[michael_knight]

Hi @danno 

In the main Dataset there is two Entities. One for Properties, which has 1 record per property. One for the Offers of the properties which has multiple records per property. 

For the Report I'm creating I'm using the offer entity. This is fine apart from this one measure I need to create. I'm creating a measure on how many properties withdraw. When I merge the entities and use this field in Offers it can show the property withdrawing multiple times due to the amount of records for a single property, which is why I need to use the Property entity instead. 

All of my slicers are either from the Date table or Offer entity, which is why I need to create an inactive relationship from Property > Offer in order to make the visual of withdrawals work with all of the slicers

Does that make sense? If not, I'll try and explain myself better 

[danno]

Hi, 

in my logic, the offer table sounds like the fact table in this scenario, where each offer is for a property, on a specific date.  Therefore the Property and Date are the dimensions.  There should only be 2 relationships between Offer and the Property and Date dimensions.  

I'm not sure I understand why the Property withdraws, not the offer?  Can you explain this logic? 

[michael_knight]

Hi,

In Property, there is a field which says whether the property has withdrawn or not. There is a Property ID which is in both Property and Offers. I merge the two entities using that ID and then expand to bring over the column that shows if it's been withdrawn or not. 

In Property there is 1 record per property. In Offers there is multiple offers per property which means that if I count the Withdrawn column I brought over to Offer with the merge it will show a multiplicated number. Because of that, I need to use Property to create the measure in order to get the accurate figure. I tried doing a distinctcount but it didn't work 

[v-easonf-msft]

Hi, @michael_knight 

I'm not entirely sure what you want.But just judging from the formula, I find that you have activated  two inactivity relationships between the table "sheep1- help" and the table "sheep2-help" at the same time.

You can only activate one relationship:
Based on  the field "Property Owner" 

Count Rows Test = CALCULATE(COUNTROWS('Sheet1 - Help'),
USERELATIONSHIP('Sheet1 - Help'[Date], 'Date'[Full Date]),
// USERELATIONSHIP('Sheet1 - Help'[Sales Owner], 'Sheet2 - Help'[Sales Owner]),
USERELATIONSHIP('Sheet1 - Help'[Property Owner],'Sheet2 - Help'[Property Owner]))

Or based on the field "Sales Owner".

Count Rows Test = CALCULATE(COUNTROWS('Sheet1 - Help'),
USERELATIONSHIP('Sheet1 - Help'[Date], 'Date'[Full Date]),
USERELATIONSHIP('Sheet1 - Help'[Sales Owner], 'Sheet2 - Help'[Sales Owner]),
// USERELATIONSHIP('Sheet1 - Help'[Property Owner],'Sheet2 - Help'[Property Owner]))

You can  also  create a new calculated columns "new column" based on "Property Owner" and "Sales Owner" , then  create a relationship based on this.

Count Rows Test = CALCULATE(COUNTROWS('Sheet1 - Help'),
USERELATIONSHIP('Sheet1 - Help'[Date], 'Date'[Full Date]),
USERELATIONSHIP('Sheet1 - Help'[new column], 'Sheet2 - Help'[new column]))

Best Regards,
Community Support Team _ Eason

[michael_knight]

Hi @v-easonf-msft 

Thanks for that. My aim is to active 3 inactive relationships. My issue is that the forumula is only allow me to activate 2 before I get an error message 

Hope that helps

Cheers,

Mike