cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SuraMan Regular Visitor
Regular Visitor

Snapshot fact table, Count Active measures

Hello,

Is there a recommended 'Power BI' way to address the below requirement?

Business process:

1) Members can subscribe to topics and unsubsribe from topics.
    Event dates of subscribing and unsubscribing of topics are recorded

Reporting and Analytical requirements:

1) List of all topics subscribed by a member on a selected date.

2) Plot the change of the subscribed topic count of a member over time
ie. X axis= Time, Y axis= Count of topics subscribed

3) List the popularity of all topics at a given date
ie. For a given date, list the topics in the order of the highest to the lowest subscription by members

4) Plot the change of the popularity of a topic over time
ie X axis= Time, Y axis= Count of members subscribed to a given topic

There are "Member" and "Topic" tables (Dimensions).
"Fact Topic subscription" table structure that I am thinking of is:

Member ID

Topic ID

Subscribed date

Unsubscribed date

A01

Books

2017-01-01

2017-01-20

A01

Games

2018-05-01

NULL

A01

Books

2017-05-05

NULL

Is this the correct approach?

I cannot figure out a straight forward way to address the above reporting requirements because of the active and inactive nature of the topic subscription and unsubscription.

How should the "Fact Topic Subscription" table be modelled in Power BI? After that, how to write the relevant measures? Simple SUM() measures will not be correct because subscriptions can be active or inactive at a given date. If the selected date falls between "subscribed date" and "unsubscribed date" of a fact table record, then the topic subscription is active and needs to be counted for that date.

Your help is appreciated.

Thanks

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
d_gosbell Senior Member
Senior Member

Re: Semi additive Snapshot fact table, Count Active measures

So the only change I would suggest to your fact table is to insert a high end date like "31-Dec-9999" instead of null when there is no Unsubscribed Date (it just makes the logic simpler.

 

Then if you create a date table (which is unreated to your fact) you can build measures with the following pattern

 

Topic Cnt = 
    CALCULATE( 
        COUNTROWS('Fact Topic subscription')
        , filter('Fact Topic subscription', MAX('Date'[Date]) >= 'Fact Topic subscription'[Subscribed date] 
                                         && MIN('Date'[Date]) < 'Fact Topic subscription'[Unsubscribed date] )
    )

You can see in the screenshot below how this picks up the end subscription of the first row and the start of the second row. (you can choose to include the Unsubscribed date in the range by changing the filter statement). You can then add addition Year, Month, Quarter, etc attributes in your Date table and the same measures should continue to work.

201905 subscriptions.png

d_gosbell Senior Member
Senior Member

Re: Semi additive Snapshot fact table, Count Active measures


@SuraMan wrote:

Hi @d_gosbell ,

I can understand that what needs to happen is to count Fact table rows where Date[date] falls between "Subscribed date" and "Unsubscribed date", but cannot figure out how that translates to the dax expression.

Because the 2 tables do not have a relationship between them if we just did a measure with COUNTROWS('Fact topic subscription') it would return a value of 3 for every date (as there are 3 rows in the example fact table). This is because without a relationship the 'Date' table will not filter the fact table at all.

 

So the expression pretty much works exactly how you've described it above to filter the fact table.

 

Topic Cnt = 
   CALCULATE( 
     COUNTROWS('Fact Topic subscription')                                    // Count the rows in the fact table
      , filter('Fact Topic subscription',                                    // filtering the fact table
MAX('Date'[Date]) >= 'Fact Topic subscription'[Subscribed date] // where the date is after Subscribed Date && MIN('Date'[Date]) < 'Fact Topic subscription'[Unsubscribed date] ) // and before the Unsubscribed Date )

The only "trick" is the MIN('Date'[Date])  and MAX('Date'[Date]) references. When you are at the grain of a single day these both return the same value. But if you had months on the rows the MIN would return the first day of the month and the MAX would return the last day of the month.

 

Is the MIN/MAX the bit that made it hard to understand?

13 REPLIES 13
d_gosbell Senior Member
Senior Member

Re: Semi additive Snapshot fact table, Count Active measures

So the only change I would suggest to your fact table is to insert a high end date like "31-Dec-9999" instead of null when there is no Unsubscribed Date (it just makes the logic simpler.

 

Then if you create a date table (which is unreated to your fact) you can build measures with the following pattern

 

Topic Cnt = 
    CALCULATE( 
        COUNTROWS('Fact Topic subscription')
        , filter('Fact Topic subscription', MAX('Date'[Date]) >= 'Fact Topic subscription'[Subscribed date] 
                                         && MIN('Date'[Date]) < 'Fact Topic subscription'[Unsubscribed date] )
    )

You can see in the screenshot below how this picks up the end subscription of the first row and the start of the second row. (you can choose to include the Unsubscribed date in the range by changing the filter statement). You can then add addition Year, Month, Quarter, etc attributes in your Date table and the same measures should continue to work.

201905 subscriptions.png

SuraMan Regular Visitor
Regular Visitor

Re: Semi additive Snapshot fact table, Count Active measures

Hi @d_gosbell ,

Thank you for your response.

Can you please help me understand how the measure Topic Cnt produces the correct result?

I can understand that what needs to happen is to count Fact table rows where Date[date] falls between "Subscribed date" and "Unsubscribed date", but cannot figure out how that translates to the dax expression.

Thanks

d_gosbell Senior Member
Senior Member

Re: Semi additive Snapshot fact table, Count Active measures


@SuraMan wrote:

Hi @d_gosbell ,

I can understand that what needs to happen is to count Fact table rows where Date[date] falls between "Subscribed date" and "Unsubscribed date", but cannot figure out how that translates to the dax expression.

Because the 2 tables do not have a relationship between them if we just did a measure with COUNTROWS('Fact topic subscription') it would return a value of 3 for every date (as there are 3 rows in the example fact table). This is because without a relationship the 'Date' table will not filter the fact table at all.

 

So the expression pretty much works exactly how you've described it above to filter the fact table.

 

Topic Cnt = 
   CALCULATE( 
     COUNTROWS('Fact Topic subscription')                                    // Count the rows in the fact table
      , filter('Fact Topic subscription',                                    // filtering the fact table
MAX('Date'[Date]) >= 'Fact Topic subscription'[Subscribed date] // where the date is after Subscribed Date && MIN('Date'[Date]) < 'Fact Topic subscription'[Unsubscribed date] ) // and before the Unsubscribed Date )

The only "trick" is the MIN('Date'[Date])  and MAX('Date'[Date]) references. When you are at the grain of a single day these both return the same value. But if you had months on the rows the MIN would return the first day of the month and the MAX would return the last day of the month.

 

Is the MIN/MAX the bit that made it hard to understand?

SuraMan Regular Visitor
Regular Visitor

Re: Semi additive Snapshot fact table, Count Active measures

Thanks @d_gosbell  for the explanation.

I used MAX('Date'[Date]) in both places because, in the month grain, I wanted to show the situation as at the end of the month.

My understanding is that what you wrote gives the number of topics that were subscribed before the begining of the month and were not unsubscribed before the end of the month, but it doesn't include topics that became active, say on the 10th of the month and continued to be active past the end of the month. I can understand that this is useful in some situations, for example, analysing active orders (ie orders that stayed active over the month)

I ended up adding +0 so that I get a value for every day.

 

I also noted that

COUNTROWS (FILTER(...)) gives the same answer (without using the Calculate statement).

Is this correct? Are there any negatives of removing the Calculate statement?

 

Thanks

 

d_gosbell Senior Member
Senior Member

Re: Semi additive Snapshot fact table, Count Active measures


@SuraMan wrote:

I used MAX('Date'[Date]) in both places because, in the month grain, I wanted to show the situation as at the end of the month.


That's fine. That is the flexibility of this model, that you can structure the filter statement to fit your particular needs (you could even have different measures using different filter expressions)

 


My understanding is that what you wrote gives the number of topics that were subscribed before the begining of the month and were not unsubscribed before the end of the month, but it doesn't include topics that became active, say on the 10th of the month and continued to be active past the end of the month. I can understand that this is useful in some situations, for example, analysing active orders (ie orders that stayed active over the month)

No, my logic is actually the reverse. It should get anything that ended after the start of the month or started before the end of the month. So it will capture subscriptions that either started or ended on the 10th of the month as having been "active" during that month.

 

I also noted that

COUNTROWS (FILTER(...)) gives the same answer (without using the Calculate statement).

Is this correct? Are there any negatives of removing the Calculate statement?

 


No, for countrows there is no real negative to removing the calculate. But if you do any other form of aggregation like MIN, MAX, SUM, etc you would probably need to use the CALCULATE pattern which is why I chose to do it that way. 

SuraMan Regular Visitor
Regular Visitor

Re: Semi additive Snapshot fact table, Count Active measures

Thanks @d_gosbell  for the explanation.

Do you have any suggestions about requrement 1 ie "List all topics subscribed by a member on a selected date." ?

From what I understand, measures cannot return a list, so is there a prescribed Power-BI-pattern to address this type of requirement?

 

d_gosbell Senior Member
Senior Member

Re: Semi additive Snapshot fact table, Count Active measures


@SuraMan wrote:

Thanks @d_gosbell  for the explanation.

Do you have any suggestions about requrement 1 ie "List all topics subscribed by a member on a selected date." ?

From what I understand, measures cannot return a list, so is there a prescribed Power-BI-pattern to address this type of requirement?

 


The same measure should meet this requirement.

 

If you create a slicer for date and member, then put this measure and the topic attribute in a visual the visual will only show topics where the measure produces a value.

SuraMan Regular Visitor
Regular Visitor

Re: Semi additive Snapshot fact table, Count Active measures

Hi @d_gosbell ,

What I want to display is a list of topics that are subscribed by a member on a selected date in a Table visual.

So, the method I am thinking is to create a measure that produces TRUE or FALSE to denote whether a given topic is subscribed by a member on a given date and use that measure in the Visual Level Filters of the table (to filter out topics when the measure is FALSE). Is this the correct and prescribed approach? At the moment, I am cannot figure out how to set a condition using a measure in "Visual Level Filters". It shows (ALL) as default and does not allow me to put a condition. (UPDATE: I converted the measure to a numerical value (1 or 0 instead of TRUE or FALSE) and then I can apply it in a Visual Level Filter)

 

Thanks

 

 

d_gosbell Senior Member
Senior Member

Re: Semi additive Snapshot fact table, Count Active measures


@SuraMan wrote:

(UPDATE: I converted the measure to a numerical value (1 or 0 instead of TRUE or FALSE) and then I can apply it in a Visual Level Filter)

So does this mean you've figured out a solution yourself now?

 

I was going to say that I would just use our original row count measure and filter for where it is >= 1