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
Achartuni
Helper I
Helper I

Remove duplicates in a DirectQuery and leave the entries with certain value in a column

Hello Power Bi community, I am running into the following problem and I'd like some help to solve this issue.

We're working with DirectQueries and we'd like to remove the duplicated values and leave the ones with the value "accepted" in the status column.

 

My table can be resumed to:

 

ID, Status

 

We currently record two entries if a student signed up and was accepted to the school program. That means, if "John" signed up to the program and got accepted he would be recorded in our table as follows:

 

John,signedup

John,accepted

Mary,signedup

 

That means that John signedup and got accpeted but Mary did not got accepted, sorry Mary.

 

Is there a way to remove duplicates and leave just the entries with the accepted status? 

 

Thanks

1 ACCEPTED SOLUTION

@Achartuni 

 

Drag measure below to Visual level filters and set Show items when the value is 1.

Measure =
IF (
    CALCULATE ( DISTINCTCOUNT ( Table1[Status] ), ALLSELECTED ( Table1[Status] ) ) = 1
        || MAX ( Table1[Status] ) = "accepted",
    1
)

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
MFelix
Super User
Super User

Hi @Achartium,

If you just want the accepted ones you can just filter the column of the status for the value accepted, however if you want to keep all other rows also the best option is to do a group by using your columns as grouping values and on the summarization for status select minimum value since accepted is before signed up.

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hello @MFelix,

 

Thank you for taking your time to comment on my thread.

 

Keeping the entries is a must that are not duplicated is a most, after all, the goal is to remove the duplicates leaving only those who are not duplicated and only one entry for those with the status as "accepted". 

 

In regards to your solution, I tried it in the Query Editor and the result was not the right for us, that means, it ended up removing all the other columns (there's more than just id  and status, I just summarized the table to simplify the question). Is there a workaround to this or are we applying your solution the wrong way? 

 

Hi  @Achartuni ,

 

Im assuming that you made the group by option correct?

 

In this case you need to place all the columns you need to choose advance, that will allow you to add more columns to your group by and also aggregations.

 

You need to select all columns that have similar values to the Groupings and all columns that have different values were you want to keep only the last one in the Aggregation.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix,

 

That's correct, we tried that option.

Sadly, since we're using DirectQuery, this workaround isn't allowed by PowerBI when working with such connection to the DB.

 

Any other suggestion would be nice.

 

Thank you

@Achartuni 

 

Drag measure below to Visual level filters and set Show items when the value is 1.

Measure =
IF (
    CALCULATE ( DISTINCTCOUNT ( Table1[Status] ), ALLSELECTED ( Table1[Status] ) ) = 1
        || MAX ( Table1[Status] ) = "accepted",
    1
)

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

What do you need to calculate?

Depending on the visualization you can select the count of max values, filter by status and so on. Depnen on what you want to achieve

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.