Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have and e-learning data with duplicates across the different columns just like this
First Name | Last Name | Course title | Status | Completion date |
James | Paul | Course A | Registered | |
James | Paul | Course A | Completed | 1/2/18 |
James | Paul | Course B | Registered | |
James | Paul | Course B | Completed | 3/5/18 |
Anne | Peter | Course A | Registered | |
Anne | Peter | Course B | Registered | |
Anne | Peter | Course C | Registered | |
Anne | Peter | Course C | Completed | 5/8/20 |
I want to create a new table with columns: [first name], [Last name], [course], [Status] using a DAX expression.
Each person would have one unique row for the completion status of each course.
If a course has been completed, I only want that row on the new table (filtering based on the completion date not blank and if no completion date exist, the status is marked "Incomplete")
For Instance:
First Name | Last Name | Course title | Status |
James | Paul | Course A | Completed |
James | Paul | Course B | Completed |
Anne | Peter | Course A | Incomplete |
Anne | Peter | Course B | Incomplete |
Anne | Peter | Course C | Completed |
Solved! Go to Solution.
Thanks for your response.
I was able to work around it using power query, relationships and DAX. The data was more complex than I explained.
Thanks for your response.
I was able to work around it using power query, relationships and DAX. The data was more complex than I explained.
@Anonymous
Create the following Table:
Table 2 = summarize('Table', [first name], [Last name], [Course title],[Completion date],"Status",IF([Completion date]=BLANK(),"Incomplete","Complete"))
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
@Anonymous , one of the two ways
summarize(filter(Table, Table[Status] ="Completed" ), [first name], [Last name], [course], [Status] )
or
calculatetable(Table, filter(Table, Table[Status] ="Completed" ))
This worked fantasic, just what I was looking for. Thanks for the help
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |