cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
msmays5
Helper I
Helper I

Calculating Measure from Two Factless Fact Tables with Dimension

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.

 

Power BI Community.JPG

1 ACCEPTED SOLUTION
OwenAuger
Super User I
Super User I

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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

3 REPLIES 3
OwenAuger
Super User I
Super User I

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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

@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.

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

 


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors