Forum Discussion

tmjones2's avatar
tmjones2
Helper I
2 years ago

Best Practice - Counting Rows in Dimension Tables (with Fact Table Between Dimensions)

I have the simple star schema model below and I want to count the number of Policies grouped by the Agency.

 

If you just use this, it doesn't work, every value is the same.

 

countrows(Policy[PolicyKey])

 

 

If you change the relationships to bi-directional, it works (but the "pros" say not to do this)

 

If you use this it works, but it's a colossal pain to remember to use crossfilter constantly

 

 

calculate(Policy[PolicyKey], Crossfilter(yadda yadda))

 

 

I'm creating a model that is going to be used by people that don't have any PBI experience at all. Are we really supposed to use one-way filtering and make them constantly use Crossfilter if they ever try to make their own measures? 

And if we go this route, are we really expecting them to drag a measure into every slicer they ever make so that the slicers are filtered based on the other applied filters?

 

Surely this cannot be the intended experience as opposed to just putting bi-directional filters everywhere in a basic star schema? I've had star schema models running for years w/ all bi-directional and have never once had any issues... 

 

Any opinions are appreciated.

 

 

Need Help
Tips and Tricks

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi tmjones2 

     

    I understand your concern about the complexity of using `CROSSFILTER` and the potential issues with enabling bi-directional relationships.

     

    Create a Measure for Counting Policies. Instead of counting rows directly, create a measure in your model that will handle the counting of policies. 

    Policy Count = COUNTROWS('Policy')

     

    If you need to count policies based on a related dimension, you can use the `RELATEDTABLE` function in your measure to filter the count based on the current filter context.

    Agency Policy Count = COUNTROWS(RELATEDTABLE('Policy'))

     

    While bi-directional relationships can make certain calculations easier, they can also lead to ambiguity and performance issues in more complex models. It's generally best to avoid them unless absolutely necessary.

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.