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.
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
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
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
