Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

nonunique dimension tables

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?

 

 

Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    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.

1 Reply

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    3 years ago

    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.