Forum Discussion
Advocate IISnapshot 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.
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?
Advocate IIHi 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
Super User
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
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
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
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?