Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Laszlo
Microsoft Employee
Microsoft Employee

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:

 

Sample.jpg

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. 

 

2 ACCEPTED SOLUTIONS

 

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

View solution in original post

@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  Datanaut

 

View solution in original post

5 REPLIES 5
AlB
Super User
Super User

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
Microsoft Employee
Microsoft Employee

Let me try to clarify the questions. 

Here is the source data: 

 

Session:      Email:

Session1user1@email1.com
Session4user2@email2.com
Session1user4@email4.com
Session4User3@email3.com
Session1User3@email3.com
Session2User3@email3.com
Session3User3@email3.com
Session5User3@email3.com
Session1user5@email5.com
Session5user5@email5.com
Session1user5@email5.com
Session2user5@email5.com
Session3user5@email5.com
Session4user5@email5.com
Session1user6@email6.com
Session1user7@email7.com
Session1user8@email8.com
Session3user8@email8.com
Session1user9@email9.com
Session1user9@email9.com
Session4user9@email9.com
Session1user10@email10.com
Session2user10@email10.com
Session1user11@email11.com
Session4user11@email11.com
Session5user11@email11.com
Session1user11@email11.com
Session1user12@email12.com
Session2user12@email12.com
Session3user12@email12.com
Session4user12@email12.com
Session5user12@email12.com

 

In PowerBI I create a table which list each email and the number of sessions they attended to: 

emailCount of session
user1@email1.com1
user10@email10.com2
user11@email11.com3
user12@email12.com5
user2@email2.com1
User3@email3.com5
user4@email4.com1
user5@email5.com5
user6@email6.com1
user7@email7.com1
user8@email8.com2
user9@email9.com2

 

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  Datanaut

Laszlo
Microsoft Employee
Microsoft Employee

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? 

@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  Datanaut

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.