Forum Discussion
Helper IICalculating Measure from Two Factless Fact Tables with Dimension
Hi msmays5
With your tables/relationships set up as they are, you could write a measure as follows:
# Articles filtered by Country and Topic = CALCULATE ( COUNTROWS ( Article ), CROSSFILTER ( 'Articles by Geography'[Article ID], Article[Article ID], Both ), CROSSFILTER ( 'Topic'[Article ID], Article[Article ID], Both ) )This measure assumes that you would be using 'Zone Leads Mapping'[Country] and 'Article Keywords'[Keyword ID] as filters on your visual.
The CROSSFILTER function with the Both argument is used to temporarily switch the relationships between your factless fact tables and Article to bidirectional, for the purpose of this measure. This effectively allows filters on 'Zone Leads Mapping' and 'Article Keywords' to filter the Article table.
Simple example attached for reference.
An alternative would be many:many relationships but I"m guessing you'd prefer not to do that given your model's setup.
Regards,
Owen
Super UserHi msmays5
With your tables/relationships set up as they are, you could write a measure as follows:
# Articles filtered by Country and Topic =
CALCULATE (
COUNTROWS ( Article ),
CROSSFILTER ( 'Articles by Geography'[Article ID], Article[Article ID], Both ),
CROSSFILTER ( 'Topic'[Article ID], Article[Article ID], Both )
)
This measure assumes that you would be using 'Zone Leads Mapping'[Country] and 'Article Keywords'[Keyword ID] as filters on your visual.
The CROSSFILTER function with the Both argument is used to temporarily switch the relationships between your factless fact tables and Article to bidirectional, for the purpose of this measure. This effectively allows filters on 'Zone Leads Mapping' and 'Article Keywords' to filter the Article table.
Simple example attached for reference.
An alternative would be many:many relationships but I"m guessing you'd prefer not to do that given your model's setup.
Regards,
Owen
msmays5OwenAuger Thanks so much, that worked! You mentioned, "With your tables/relationships set up as they are...." Do you think there is a better way to approach this? I'm still a bit inexperienced on the data modeling side, so any ideas are greatly appreciated.
- OwenAuger
msmays5 You're welcome!
Actually the way you've set up your model is the traditional way of handling many-to-many relationships, which is great!
I was wondering whether explicit many-to-many relationships would help, but I would actually avoid those where possible. The only change I would suggest is making the relationships between 'Articles by Geography' & Article and Topic & Article bidirectional.
This would mean 'Zone Leads Mapping' and 'Article Keywords' can filter the Article table without use of the CROSSFILTER function, and shouldn't cause any unintended problems with the model from what I can see.
So you can just write
# Articles = COUNTROWS ( Article )