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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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