Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
@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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
20 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |