Forum Discussion
active the relationship and create measure
Hi, I am making a report that represent sale achievement rate among two years.
I want to create measure(% achievement rate) in target table.
The problem is the relationship between date table and target is inactive. I know USERELATIONSHIP() can active the relationship but i don't how to deal with it exactly. Thanks
Hey Anonymous ,
you can activate an inactive relationship using USERELATIONSHIP as you already mentioned but from the picture you provided, it seems you have to "deactivate" the existing relationship between the table "Date table" and "Sales" to avoid circular dependencies. This can be done by creating a measure like so:
measure = CALCULATE( <the formula that creates a values> //the calculation of the value , USERELATIONSHIP( 'Date table'[Year] , 'Target'[Target Year] ) //activates an inactive relationship , CROSSFILTER( 'Date Table'[Date] , 'Sales'[Date] , None ) //deactivates an existing relationship )Hopefully, this provides what you are looking for.
Regards,
Tom
2 Replies
- amitchandak
Super User
Anonymous , the Client Should join with both sales and target
Sales and Target should not join
the date should join to both tables
We should try for Star schema - No to facts join with each other (sales and target) no dimensions join with each other.
Facts and dimensions join
https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/
- TomMartens
Hey Anonymous ,
you can activate an inactive relationship using USERELATIONSHIP as you already mentioned but from the picture you provided, it seems you have to "deactivate" the existing relationship between the table "Date table" and "Sales" to avoid circular dependencies. This can be done by creating a measure like so:
measure = CALCULATE( <the formula that creates a values> //the calculation of the value , USERELATIONSHIP( 'Date table'[Year] , 'Target'[Target Year] ) //activates an inactive relationship , CROSSFILTER( 'Date Table'[Date] , 'Sales'[Date] , None ) //deactivates an existing relationship )Hopefully, this provides what you are looking for.
Regards,
Tom
