Calculating Measure from Two Factless Fact Tables with Dimension

[msmays5]

I have the below model. Each artcle has two type of tags: Geography and Topic. I'm trying to calculate the # of Articles each country has for each topic. While I feel like this should be easy, I'm struggling for some reason. Any help would be greatly appreciated.

[OwenAuger · Accepted solution]

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

[msmays5]

@OwenAuger 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 )