cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thakks Regular Visitor
Regular Visitor

Slicer Relationship with snowflake Dimesnion

I have this measure named Sales Order, Purchase Order and Snowflake Dimension named Product and Category. I have slicer on Category ID on the report.  Now on the report, when i slice by CategoryID, I want for Sales Order that Product join to Category normally on Category ID  BUT for Purchase Order it join on Category through LegacyCategoryID! So we only have one slicer for Category on the report.

 

I am struggling how to achieve this. I am just mocking up the scenario to make it simple but the real reason we need this working is because we have around 10 data set, of which half are joined to this dependent category table differently and we really do not want two slicer on the report.

 

Thanks

 

Measure - Sales Order

SalesOrderID, ProductID, Amount

 

Measure - Purchase Order

PurchaseOrderID, ProductID, Amount

 

Dimension - Product

ProductID, CategoryID, LegacyCategoryID

 

Snowflake - Category

CategoryID, Category Description

 

Slicer CategoryID

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Slicer Relationship with snowflake Dimesnion

Use CALCULATE and USERELATIONSHIP. If you want more specificity...Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


2 REPLIES 2
Super User
Super User

Re: Slicer Relationship with snowflake Dimesnion

Use CALCULATE and USERELATIONSHIP. If you want more specificity...Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


thakks Regular Visitor
Regular Visitor

Re: Slicer Relationship with snowflake Dimesnion

Based on @Greg_Deckler suggestion, i got this working by 

First having one active relationship (Product.CategoryID=Category.CategoryID) and other inactive relationship (Product.LegacyCategoryID=Category.CategoryID)

I then created new measure on PurchaseOrder as 

Calculate(sum(PurchaseOrder[Amount]),ALLSELECTED('Product'), USERELATIONSHIP(Product.[LegacyCategoryID]=Category.[CategoryID])