nonunique dimension tables

[Anonymous]

Hi, 

I have a "Ticket" Fact table which is unique on Ticket Number.
Each ticket can have MANY labels, products, and components.

I'm trying to get the Label, Product, and Component tables to be dimensions tables so I can filter on them.

If I join the label table to the fact table with a cross-filter direction of "both", it works fine. Then I join the component table to the fact table with a cross filter direction of "both". After that, the visualizations break.

I have tried a few iterations with bridge tables, but it hasn't worked.
Any ideas on how to handle a model that has nonunique dimension tables?

[DataInsights · Accepted solution]

@Anonymous,

Try this solution.

Create a relationship between each dimension table and the fact table (crossfilter direction = single):

Measure:

Total Hours = 
CALCULATE (
    SUM ( FactTable[Hours] ),
    CROSSFILTER ( LabelTable[Ticket #], FactTable[Ticket #], BOTH ),
    CROSSFILTER ( ProductTable[Ticket #], FactTable[Ticket #], BOTH )
)

Create slicers using dimension tables, and add FactTable[Ticket #] to a visual:

You can use this pattern for additional dimension tables. In the measure, add a CROSSFILTER argument for each dimension table, enabling bidirectional crossfiltering in the measure.