Forum Discussion

Laszlo's avatar
Laszlo
Microsoft Employee
6 years ago
Solved

Report on multiple course completion

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. 

 

Need Help
Tips and Tricks
  • AlB's avatar
    AlB
    6 years ago

     

    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  Datanaut

  • AlB's avatar
    AlB
    6 years ago

    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
    [email protected] 1
    [email protected] 2
    [email protected] 3
    [email protected] 5
    [email protected] 1
    [email protected] 5
    [email protected] 1
    [email protected] 5
    [email protected] 1
    [email protected] 1
    [email protected] 2
    [email protected] 2

     

     

    Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

    Cheers  Datanaut

     

5 Replies