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
Date | user_id | content_id | id |
2021-10-14 | 5 | 1 | 1 |
2021-10-14 | 8 | 1 | 2 |
2021-10-14 | 8 | 1 | 3 |
2021-10-14 | 8 | 6 | 4 |
Solved! Go to Solution.
You'd need to create a table that contains all possible sessions per user.
Something like this:
X-Axis =
VAR MaxSessions = MAXX ( ALL ( Sessions[user_id] ), CALCULATE ( COUNT ( Sessions[id] ) ) )
RETURN
SELECTCOLUMNS ( GENERATESERIES ( 0, MaxSessions + 1 ), "Sessions", [Value] )
The measures have to be a bit more complicated.
AvgUniqueContent =
VAR CurrSessions = SELECTEDVALUE ( 'X-Axis'[Sessions] )
VAR Summary =
SUMMARIZE (
Sessions,
Sessions[user_id],
"@UniqueContent", DISTINCTCOUNT ( Sessions[content_id] ),
"@Sessions", COUNT ( Sessions[id] )
)
RETURN
AVERAGEX ( FILTER ( Summary, [@Sessions] = CurrSessions ), [@UniqueContent] )
The user count can be defined similarly (just use COUNTROWS instead of AVERAGEX in the last line).
This should be possible without a calculated table. Please clarify what each bubble represents. Is each one a User? a month? You gave the expect Y axis values. What are the expected values for the X axis and bubble size? I thought each bubble was a user until I saw that size was distinct count of users.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
X-axis is the session count for a user. Size is the number of users with this session count. If you have three users and one has 3 sessions and the other two have 5 sessions each, then you get a bubble with size one at x=3 and a bubble with size two at x=5.
I'd be super interested if you can find a way to do this dynamically without setting up a table to use for the x-axis.
This is easier if you create a calculated summary table like this:
From there, it's drag-and-drop to put the fields into a scatterplot.
If it needs to be more dynamic (where a calculated table doesn't work), you'll still need to create an extra table to use for the x-axis.
Thanks a lot for your detailed answer, it's very much appreciated! This does work indeed, but unfortunately I need to be able to filter on month even and even on a category which is in another dimension linked by content_id. What would my table structure need to look like to make that work?
Thanks :)!
You'd need to create a table that contains all possible sessions per user.
Something like this:
X-Axis =
VAR MaxSessions = MAXX ( ALL ( Sessions[user_id] ), CALCULATE ( COUNT ( Sessions[id] ) ) )
RETURN
SELECTCOLUMNS ( GENERATESERIES ( 0, MaxSessions + 1 ), "Sessions", [Value] )
The measures have to be a bit more complicated.
AvgUniqueContent =
VAR CurrSessions = SELECTEDVALUE ( 'X-Axis'[Sessions] )
VAR Summary =
SUMMARIZE (
Sessions,
Sessions[user_id],
"@UniqueContent", DISTINCTCOUNT ( Sessions[content_id] ),
"@Sessions", COUNT ( Sessions[id] )
)
RETURN
AVERAGEX ( FILTER ( Summary, [@Sessions] = CurrSessions ), [@UniqueContent] )
The user count can be defined similarly (just use COUNTROWS instead of AVERAGEX in the last line).
That's awesome, thanks again for the detailed explanation. This works great!!
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 |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
248 | |
124 | |
111 | |
78 | |
78 |