Report on multiple course completion

[Laszlo]

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. 

[AlB · Accepted 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  

[AlB]

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. 

[Laszlo]

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: 

email	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!

[AlB · Accepted 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  

[Laszlo]

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? 

[AlB · Accepted solution]

@Laszlo 

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 

email	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