cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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:

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.

2 ACCEPTED SOLUTIONS
Super User

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_ =
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

Super User

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

5 REPLIES 5
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).

Employee

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!

Super User

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_ =
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

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?

Super User

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors