Forum Discussion
Report on multiple course completion
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
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
Let me try to clarify the questions.
Here is the source data:
Session: Email:
| Session1 | [email protected] |
| Session4 | [email protected] |
| Session1 | [email protected] |
| Session4 | [email protected] |
| Session1 | [email protected] |
| Session2 | [email protected] |
| Session3 | [email protected] |
| Session5 | [email protected] |
| Session1 | [email protected] |
| Session5 | [email protected] |
| Session1 | [email protected] |
| Session2 | [email protected] |
| Session3 | [email protected] |
| Session4 | [email protected] |
| Session1 | [email protected] |
| Session1 | [email protected] |
| Session1 | [email protected] |
| Session3 | [email protected] |
| Session1 | [email protected] |
| Session1 | [email protected] |
| Session4 | [email protected] |
| Session1 | [email protected] |
| Session2 | [email protected] |
| Session1 | [email protected] |
| Session4 | [email protected] |
| Session5 | [email protected] |
| Session1 | [email protected] |
| Session1 | [email protected] |
| Session2 | [email protected] |
| Session3 | [email protected] |
| Session4 | [email protected] |
| Session5 | [email protected] |
In PowerBI I create a table which list each email and the number of sessions they attended to:
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
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?
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