The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Community!
Trying to get my head around the correct DAX to essentially look at event takeup as the date of the event nears. The visual should be filterable by region of the event e.g. takeup of all the events hosted in London, date of the event, and by data points of the individuals making the booking e.g. their city of residence.
I have:
Fact_Bookings - master list of all bookings made by individuals to all events ever hosted. Includes date of signup, date of event, calculation of 'signup days in advance', bins of signup advance days (the variable I want on my X axis)
Dim_Events - master list of all events ever hosted, and their maximum capacity. The unique ID for this is a concatenation of Location (non-unique) and Date (unique)
I've tried DAX on a calculated column to show, for each booking, what the event takeup is after that booking i.e. (cumulative bookings for that event / maximum capacity of event), attempted along the lines of
Cumulative bookings =
Calculate(
Count(Fact_Bookings[user ID]),
relatedtable(Dim_Events),
Fact_Bookings[signup date] <= earlier(Fact_Bookings[signup date]))
but there's clearly things wrong with this, even as a count rather than as a % of the relevant event's max capacity.
Any help would be much appreciated, thanks!!
@jonnyko , I think you can create a new column like in Fact_Bookings
countx(filter(Fact_Bookings, Fact_Bookings[signup date] <= earlier(Fact_Bookings[signup date])),Fact_Bookings[user ID])
if not
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
It's not letting me post with a table because it says invalid HTML (no idea why). I've attached a screenshot of the data tables I'm working with and the desired column/calculation at the end instead hope that clarifies things for you?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
67 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
124 | |
40 | |
30 | |
24 | |
22 |