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.
Hello,
I have a table listing a number of events with the corresponding start and end dates and other attributes, e.g.:
id | start_date | end_date | country | type | public |
e01 | 2021/03/13 | 2021/07/19 | Germany | Exhibition | yes |
e02 | 2021/05/15 | 2021/06/18 | France | Market | yes |
e03 | 2021/09/23 | 2021/11/20 | Spain | Market | yes |
e04 | 2021/04/22 | 2021/08/29 | Italy | Exhibition | no |
e05 | 2021/06/19 | 2021/09/25 | Spain | Market | yes |
e06 | 2021/09/14 | 2021/09/23 | Germany | Exhibition | yes |
e07 | 2021/08/18 | 2021/10/30 | Germany | Committee | no |
e08 | 2021/08/27 | 2021/11/15 | France | Exhibition | no |
e09 | 2021/10/16 | 2021/11/17 | Italy | Committee | yes |
e10 | 2021/06/20 | 2021/08/22 | France | Committee | no |
I need to create a graph of the number of events active in each of the last 12 months, something like this:
I'm struggling to find a suitable way to create the graph: I am not able to create an intermediate static table with DAX because I need to allow filtering of the data based on several attributes and to drill down to the actual event data. To create the example I just duplicate each event line for each of the months it lasts, but I can't use the same strategy with the real data because I have tens of thousands of events each spanning several months and with tens of attributes and I don't really want to deal with half million records.
Is there a clever way to count the events in each histogram bar based on an arbitrary condition? I mean, each bar should basically count the events where:
start_date <= end_of_month AND end_date >= start_of_month
but I can't find how to implement it. Are there any other way to solve this problem?
Thanks in advance
Solved! Go to Solution.
Thanks, that's an interesting approach. This can minimize the space used to store the data and would keep the initial table "clean" for other calculations.
Playing around with measures, I think I've found a possible solution using just the original data:
Count of id in month =
VAR __SELECTED = SELECTEDVALUE( 'months'[month])
VAR __MONTH_START = DATE( YEAR( __SELECTED), MONTH( __SELECTED), 1)
VAR __MONTH_END = EOMONTH( __SELECTED, 0)
RETURN
CALCULATE( COUNTA( 'events'[id]),
'events'[start_date] <= __MONTH_END, 'events'[end_date] >= __MONTH_START
)
It needs a "months" table with a list of the nonths to be represented in the chart:
month
2021/03/01 |
2021/04/01 |
2021/05/01 |
(etc...) |
This seems quite fast and flexible as I can easily create different measure for different views (e.g. events starting and ending in the same month, new events for the month, etc.), but I'm not sure if it solid enough or if it will bring any issue while I proceed with the implementation.
One problem my solution has compared to your proposal is that it returns just a single value for each data point so I can't use the "show data point as a table" menu or navigate the data.
I will probably try to implement both on the real data and see which one suits better the reporting objectives.
Thanks a lot
You're on the right track. You need to generate intermediate tables, but you need to do that inside your measure. For each month of your x axis value you need to compute if the event was active in that month. This requires a disconnected table for your x axis value. The problem with this approach is that you have to hard code the measure for each attribute's values.
"but I can't use the same strategy with the real data because I have tens of thousands of events each spanning several months and with tens of attributes and I don't really want to deal with half million records."
The number of attributes will not impact the number of rows, it will just widen the table. If you have tens of thousands of events and they span over ten months each then you only end up with hundred thousand rows. Even 500K rows is still a reasonably small number. Plus, in this scenario you can do all the prep work in Power Query so you only have to do it once per refresh.
Is that sufficient to get you going or would you like to see a sample implementation?
Thanks, that's an interesting approach. This can minimize the space used to store the data and would keep the initial table "clean" for other calculations.
Playing around with measures, I think I've found a possible solution using just the original data:
Count of id in month =
VAR __SELECTED = SELECTEDVALUE( 'months'[month])
VAR __MONTH_START = DATE( YEAR( __SELECTED), MONTH( __SELECTED), 1)
VAR __MONTH_END = EOMONTH( __SELECTED, 0)
RETURN
CALCULATE( COUNTA( 'events'[id]),
'events'[start_date] <= __MONTH_END, 'events'[end_date] >= __MONTH_START
)
It needs a "months" table with a list of the nonths to be represented in the chart:
month
2021/03/01 |
2021/04/01 |
2021/05/01 |
(etc...) |
This seems quite fast and flexible as I can easily create different measure for different views (e.g. events starting and ending in the same month, new events for the month, etc.), but I'm not sure if it solid enough or if it will bring any issue while I proceed with the implementation.
One problem my solution has compared to your proposal is that it returns just a single value for each data point so I can't use the "show data point as a table" menu or navigate the data.
I will probably try to implement both on the real data and see which one suits better the reporting objectives.
Thanks a lot
One other issue with the measure approach is that it needlessly recomputes all the time. Your data is immutable and doesn't need recomputing.
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |