Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SuraMan
Advocate II
Advocate II

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
d_gosbell
Super User
Super User

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

View solution in original post


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

View solution in original post

13 REPLIES 13
d_gosbell
Super User
Super User

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

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


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

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

 


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

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?

 


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

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

 

 


@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

Hi @d_gosbell ,

I ended up doing it the way I described, but I wasn't sure that is the correct way.

In general, if we want to restrict a list of data rows shown in a table (or any other visual) based on a measure, is the correct approach to put a conditional filter using that measure into the "Visual Level Filter"?

 


@SuraMan wrote:

Hi @d_gosbell ,

I ended up doing it the way I described, but I wasn't sure that is the correct way.

In general, if we want to restrict a list of data rows shown in a table (or any other visual) based on a measure, is the correct approach to put a conditional filter using that measure into the "Visual Level Filter"?

 


If you just want specific visuals filtered by this measure then putting a conditional filter based on that measure into the "Visual Level Filter" is fine. But if you want every visual on a page filtered by this measure then I'd move the filter up to the page level or to the report level if you want the whole report filtered that way.

 

In terms of query performance it does not matter which level you define the filters at as they all get merged together when the query for a given visual is generated. But it makes a big difference to how easy it is to edit the report in future if you ever need to alter the filters or duplicate a page and apply a different set of filter conditions.



 


If you just want specific visuals filtered by this measure then putting a conditional filter based on that measure into the "Visual Level Filter" is fine. But if you want every visual on a page filtered by this measure then I'd move the filter up to the page level or to the report level if you want the whole report filtered that way.

 

Hi @d_gosbell ,

Can measures be included in Page level and Report level filters? The below article says they cannot. Power BI does not allow me to place a measure in Page level or Report level filter.

The Visual Level Filters section is the only one accepting a measure as a filter, whereas Page Level Filters and Report Level Filters only accept columns as a filter

https://www.sqlbi.com/articles/applying-a-measure-filter-in-power-bi/

 


@SuraMan wrote:

Can measures be included in Page level and Report level filters? The below article says they cannot. Power BI does not allow me to place a measure in Page level or Report level filter.

 


No, you are right, the page/report level filters don't accept measures as filters.

 

I find this lack of consistency annoying, I don't think there is any logical reason for this. Filters based on measures are potentially worse from a performance perspective, but if you need that functionality this just forces you to apply the filter at the visual level.

 

There is a different arbitrary restriction like this on Slicers which don't accept any Visual level filters, not because of any technical reason, just because someone at Microsoft decided that people probably would not need to do this.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.