topic Re: Cross Filter Multiple Fact Tables in DAX Commands and Tips

Cross Filter Multiple Fact Tables

msmays5 — Fri, 06 Nov 2020 23:43:57 GMT

Hi everyone,

I have the following tables, plus a date table (fact tables in green, dimension tables in yellow)

There are the following relationships:

1. 'Articles'[ArticleID] --> 'Article Views'[ArticleId]

2. 'Articles'[ArticleID] --> 'Article Tags'[ArticleId]

3. 'Countries'[Country] --> 'Article Tags'[Country]

4. 'DateTable'[Date] --> 'Article Views'[Date]

GOAL: Display the % of Articles Assigned to a Country with Incremental Views In Month. In other words: Count the articles visible to each country if that article had an incrmental view in the selected month and divide by all articles visible to the country

I have the following measures already created, but I'm not sure how to calculate the above:

# of Articles Visible to Country:= CALCULATE ( DISTINCTCOUNT ( 'Article Tags'[ArticleId] ), CROSSFILTER ( 'Articles'[ArticleId], 'Articles Tags'[ArticleId], BOTH ) ) --------------------------------------------------------------------------------- # of Incremental Article Views in Month:= VAR _NumberInLatestMonth = [# of Cumulative Article Views] VAR _NumberInPriorMonth = CALCULATE ( [# of Cumulative Article Views], PREVIOUSMONTH ( DateTable[Date] ) ) RETURN _NumberInLatestMonth - _NumberInPriorMonth

Any help would be greatly appreicated! If anything I listed isn't clear, please let me know. Thanks so much

Re: Cross Filter Multiple Fact Tables

ctaulbee — Sat, 07 Nov 2020 00:40:01 GMT

It's a little hard to follow what you're hoping to accomplish. Not sure what you mean by an incremental view.

If you have cumulative counts by month in your first fact table, does that mean if you simply sum 2 months you'd be double counting the first month?

Also, you have no country identifier in your first fact table, so if you take a sum of the article views and divide it by total number of countries who had access to that article, what would that tell you?

Re: Cross Filter Multiple Fact Tables

msmays5 — Sat, 07 Nov 2020 01:49:05 GMT

ctaulbee An incremental view is a new view in a given month. So articles 1, 2, and 3 each had incrmental views in February, while articles 4 and 5 didn't. Yes, you are correct that summing cumulative views would be double counting.

You are also correct that the Article Views table doesn't have a country identifier - that's because we don't actually have that data available in the data source. So what I want to understand is that, among articles tagged to a particular country, what % had incremental views in a given month. To be specific, Canada has access to articles 1 and 2, each of which had incremental views in Februrary, so the value would be 100% (2/2). Mexico has access to two articles 1 and 5. Because articles 1 had incremental views in February but article 2 didn't, the value for Mexico would be 50%.

I agree it's not the mose meaningful metric, but it's what I've been asked to provide.

Re: Cross Filter Multiple Fact Tables

CNENFRNL — Sat, 07 Nov 2020 16:11:05 GMT

msmays5 , although your senario isn't that logical (the Article Views table doesn't have a country identifier), I came up with a solution in line with your description; you might want to refer to the attached file for details.

Measure = VAR __articles = CALCULATETABLE ( DISTINCT ( dArticle[ArticleID] ), CALCULATETABLE ( fTag ) ) RETURN DIVIDE ( SUMX ( __articles, VAR __prev = CALCULATE ( [Views], PREVIOUSMONTH ( dDate[Date] ) ) RETURN ( [Views] > __prev ) + 0 ), [# of Articles Visible to Country] )

Re: Cross Filter Multiple Fact Tables

msmays5 — Tue, 17 Nov 2020 22:48:20 GMT

CNENFRNL Sorry for the delayed response; when I first implemented the measure, it wasn't working for me, but I realized that my model had a relationship that caused it to not be in star schema. Once I fixed this, your answer worked perfectly! Thank you so much for your help!

Re: Cross Filter Multiple Fact Tables

Nickiman — Mon, 30 Aug 2021 10:51:08 GMT

I have 2 fact tables that I would like to consolidate in a matrix viz that match/ align the (dates/transaactions) rows from those 2 fact tables , any suggestion pls? any help is much appreciated thank you so much!