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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Create a new table based on filters

I have and e-learning data with duplicates across the different columns just like this

First NameLast NameCourse titleStatusCompletion date
JamesPaulCourse ARegistered 
JamesPaulCourse ACompleted1/2/18
JamesPaulCourse BRegistered 
JamesPaulCourse BCompleted3/5/18
AnnePeterCourse ARegistered 
AnnePeterCourse BRegistered 
AnnePeterCourse CRegistered 
AnnePeterCourse CCompleted5/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 NameLast NameCourse titleStatus
JamesPaulCourse ACompleted
JamesPaulCourse BCompleted
AnnePeterCourse AIncomplete
AnnePeterCourse BIncomplete
AnnePeterCourse CCompleted
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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.

V-pazhen-msft
Community Support
Community Support

@Anonymous 

Create the following Table:

Table 2 = summarize('Table', [first name], [Last name], [Course title],[Completion date],"Status",IF([Completion date]=BLANK(),"Incomplete","Complete"))

Vpazhenmsft_0-1630997755430.png

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors