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.
Hi,
I have two tables. One "subscriber" stores subscriber and unsubscriber values for a timestamp.
The other table has only one product category with a creation date:
Now I want to show visually in a bar chart, how many subscribers were present when the category was created and how many after one day:
Table subscriber:
date_time | calc_subscriber | calc_unsubscriber |
2021-07-06 12:00 | 12 | 5 |
2021-07-06 12:15 | 14 | 8 |
2021-07-06 12:30 | 0 | 9 |
2021-07-06 12:45 | 24 | 3 |
2021-07-06 13:00 | 52 | 24 |
2021-07-07 12:15 | 120 | 1 |
2021-07-07 12:30 | 238 | 10 |
2021-07-08 12:30 | 3 | 0 |
2021-07-08 12:30 | 4 | 0 |
Table catagory:
created_at | cat_name |
2021-07-06 12:30 | a |
2021-07-07 08:00 | b |
Result:
cat_name | subscriber on created | subscriber on created + 1 day |
a | 13 | 400 |
b | 53 | 407 |
For this purpose, I have calculated a comulative subscriber value via a calculate coulmn:
comulative subscriber_on_created =
CALCULATE(
SUM(subscribers[calc_subscribers]),FILTER(ALL(subscribers),subscribers[date_time] <= category[created_at])))-
CALCULATE(
SUM(subscribers[calc_unsubscribers]),FILTER(ALL(subscribers),subscribers[date_time] <= category[created_at])))
Now I am still looking for a way for the second calculated column. I tried for category [created_at] with Dateadd(date,1,day), Date (year, month, day + 1), date.dateAdd.
And second question. Is this the most performant way with one million entries in subscriber?
Best regards
Solved! Go to Solution.
Hi @MarcS
You can try modifying the column code to below one.
comulative subscriber_on_created =
CALCULATE (
SUM ( subscribers[calc_subscribers] ) - SUM ( subscribers[calc_unsubscribers] ),
FILTER (
ALL ( subscribers ),
subscribers[date_time] < ( category[created_at] + 1 )
)
)
It seems you are adding calculated columns in Table category. If so, it will enlarge the model size a little bit. This is ok. If you add 20 of these columns to Table subscriber with over 1 mio entries, the model size will be very large. You could use measures rather than calculated columns to avoid enlarging model size.
Create a base measure
comulative subscriber = SUM ( subscribers[calc_subscribers] ) - SUM ( subscribers[calc_unsubscribers] )
Then create measures for different days. You can modify the days variable to create 20 measures.
comulative subscriber + 1 day =
VAR days = 1 // change days value from 0 to 20
RETURN
CALCULATE (
[comulative subscriber],
FILTER (
ALL ( subscribers ),
subscribers[date_time] < MAX ( category[created_at] ) + 1 + days
)
)
You can also pass a what-if parameter or axis values to the days variable in this measure when you want it to change dynamically according to context in visuals.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
After a little more research i found a trivial solution:
comulative subscriber_on_created =
CALCULATE(
SUM(subscribers[calc_subscribers]),FILTER(ALL(subscribers),subscribers[date_time] <= (category[created_at]+1)))-
CALCULATE(
SUM(subscribers[calc_unsubscribers]),FILTER(ALL(subscribers),subscribers[date_time] <= (category[created_at]+1)))
+1 adds directly 1 day to the timestamp. Little bit confusing but it works.
Open is the question about the performance for more than one calculated colum. If i have 20 of these calculate columns for +1 day +2 days +5 days .... with a subscriber table with over 1 mio entries. Is there a better solution here.
Hi @MarcS
You can try modifying the column code to below one.
comulative subscriber_on_created =
CALCULATE (
SUM ( subscribers[calc_subscribers] ) - SUM ( subscribers[calc_unsubscribers] ),
FILTER (
ALL ( subscribers ),
subscribers[date_time] < ( category[created_at] + 1 )
)
)
It seems you are adding calculated columns in Table category. If so, it will enlarge the model size a little bit. This is ok. If you add 20 of these columns to Table subscriber with over 1 mio entries, the model size will be very large. You could use measures rather than calculated columns to avoid enlarging model size.
Create a base measure
comulative subscriber = SUM ( subscribers[calc_subscribers] ) - SUM ( subscribers[calc_unsubscribers] )
Then create measures for different days. You can modify the days variable to create 20 measures.
comulative subscriber + 1 day =
VAR days = 1 // change days value from 0 to 20
RETURN
CALCULATE (
[comulative subscriber],
FILTER (
ALL ( subscribers ),
subscribers[date_time] < MAX ( category[created_at] ) + 1 + days
)
)
You can also pass a what-if parameter or axis values to the days variable in this measure when you want it to change dynamically according to context in visuals.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |