Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
Here is the problem I am trying to solve and so far failed in PowerBI.
I have a lot of customers attending multiple workshops and there are 5 workshops in the course. I can create a table which contains all the data I need to aggregate. Customers, unique email address per customer, unique session attendance.
All the unique columns are counting unsig distinct selection so my end result table is something like:
What I would like to create a table or visual for is how many of the customers completed all 5 sessions in the course. How many completed 4, 3, 2 and 1. I am able to get a visual showing how many customers completed workshop 1, 2 etc, but not how many have compelted all 5, 4, 3 etc.
Sessions completed are also not sequential. Some customer completed session 1,3 and 4 for a total of three, while another customer completed session 1,2,3 for a total of three.
Using the sample above, 3 customer completed all 5 workshops, 2 customers 4 workshops, 3 customers 3 workshops, 1 customer 2 and 1 customer 1 workshops.
Thanks in advance.
Solved! Go to Solution.
1. Create an aux table:
AuxTable = GENERATESERIES(1,5) //Change the number of sessions to show as required
2. Place AuxTable[Value] in the rows of a table visual
3. Create this measure and place it in the table visual:
Measure = VAR CountT_ = ADDCOLUMNS ( DISTINCT ( Table1[email] ), "CountCol", CALCULATE ( COUNT ( Table1[email] ) ) ) RETURN COUNTROWS ( FILTER ( CountT_, [CountCol] = SELECTEDVALUE ( AuxTable[Value] ) ) )
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
Cheers
AuxTable_ is used as base to show 1,2,...,5 in the visual and to slice on that.
The measure first builds the table (CountT_) that you were showing internally and secondly, counts how many rows there are in CountT_ with the number specified on the row of the visual (1,2,...,5)
CountT_ looks like this internally
Count of session | |
user1@email1.com | 1 |
user10@email10.com | 2 |
user11@email11.com | 3 |
user12@email12.com | 5 |
user2@email2.com | 1 |
User3@email3.com | 5 |
user4@email4.com | 1 |
user5@email5.com | 5 |
user6@email6.com | 1 |
user7@email7.com | 1 |
user8@email8.com | 2 |
user9@email9.com | 2 |
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
Cheers
Hi @Laszlo
Can you provide the actual data sample in tabular format? The data shown doesn't match what you describe.
Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).
Please have a look at these tips for getting your question answered quickly.
Let me try to clarify the questions.
Here is the source data:
Session: Email:
Session1 | user1@email1.com |
Session4 | user2@email2.com |
Session1 | user4@email4.com |
Session4 | User3@email3.com |
Session1 | User3@email3.com |
Session2 | User3@email3.com |
Session3 | User3@email3.com |
Session5 | User3@email3.com |
Session1 | user5@email5.com |
Session5 | user5@email5.com |
Session1 | user5@email5.com |
Session2 | user5@email5.com |
Session3 | user5@email5.com |
Session4 | user5@email5.com |
Session1 | user6@email6.com |
Session1 | user7@email7.com |
Session1 | user8@email8.com |
Session3 | user8@email8.com |
Session1 | user9@email9.com |
Session1 | user9@email9.com |
Session4 | user9@email9.com |
Session1 | user10@email10.com |
Session2 | user10@email10.com |
Session1 | user11@email11.com |
Session4 | user11@email11.com |
Session5 | user11@email11.com |
Session1 | user11@email11.com |
Session1 | user12@email12.com |
Session2 | user12@email12.com |
Session3 | user12@email12.com |
Session4 | user12@email12.com |
Session5 | user12@email12.com |
In PowerBI I create a table which list each email and the number of sessions they attended to:
Count of session | |
user1@email1.com | 1 |
user10@email10.com | 2 |
user11@email11.com | 3 |
user12@email12.com | 5 |
user2@email2.com | 1 |
User3@email3.com | 5 |
user4@email4.com | 1 |
user5@email5.com | 5 |
user6@email6.com | 1 |
user7@email7.com | 1 |
user8@email8.com | 2 |
user9@email9.com | 2 |
Back to my questions:
I need to count all the users who attended 5, 4, 3, 2,1 sessions. I don't need to know at the point which sessions did they attended when the number is not 5, just that they have attended a number of sessions.
I hope my example helps clear things up.
Thanks again!
1. Create an aux table:
AuxTable = GENERATESERIES(1,5) //Change the number of sessions to show as required
2. Place AuxTable[Value] in the rows of a table visual
3. Create this measure and place it in the table visual:
Measure = VAR CountT_ = ADDCOLUMNS ( DISTINCT ( Table1[email] ), "CountCol", CALCULATE ( COUNT ( Table1[email] ) ) ) RETURN COUNTROWS ( FILTER ( CountT_, [CountCol] = SELECTEDVALUE ( AuxTable[Value] ) ) )
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
Cheers
Thanks very much for your help! I got it working in my test table and will transfer over to my production tables.
Just so I understand it correctly what is happening.
The external table used as a bucket to store the infromation in.
CountT_ counts the unique email addresses.
Return is using CountT_ and stores it in the external table.
Where does this measure picks up the session count as I don't see sessions being queried at all?
AuxTable_ is used as base to show 1,2,...,5 in the visual and to slice on that.
The measure first builds the table (CountT_) that you were showing internally and secondly, counts how many rows there are in CountT_ with the number specified on the row of the visual (1,2,...,5)
CountT_ looks like this internally
Count of session | |
user1@email1.com | 1 |
user10@email10.com | 2 |
user11@email11.com | 3 |
user12@email12.com | 5 |
user2@email2.com | 1 |
User3@email3.com | 5 |
user4@email4.com | 1 |
user5@email5.com | 5 |
user6@email6.com | 1 |
user7@email7.com | 1 |
user8@email8.com | 2 |
user9@email9.com | 2 |
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
Cheers
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
100 | |
98 | |
88 | |
70 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |