Condition to Eliminate Duplicate Count Items

[Anonymous]

I'm still looking for an answer to the following. Thank you!

Source Community: Power BI

I'm extracting data from an SP list. The list consists of qualifications and certifications (years 1989 - 2020).

Some of the qualifications are repeats, as the employee needs to requalify periodically (once per year, every 2 years, every 3 years), depending on type of qualifications. In BI the counts should only include the most recent of these qualificaitons, not all in the list.

In Power Apps I have a condition that eliminates duplication and only shows the most recent items that appear more than once in the list. Is this possible in BI?

Is there a way to implement the same conditions in the data I'm incorporating into the BI screen?

[Anonymous]

I'm extracting data from an SP list. The list consists of qualifications and certifications (years 1989 - 2020).

Some of the qualifications are repeats, as the employee needs to requalify periodically (once per year, every 2 years, every 3 years), depending on type of qualifications. In BI the counts should only include the most recent of these qualificaitons, not all in the list.

In Power Apps I have a condition that eliminates duplication and only shows the most recent items that appear more than once in the list. Is this possible in BI?

Is there a way to implement the same conditions in the data I'm incorporating into the BI screen?

[v-stephen-msft]

Hi @Anonymous ,

1.In the Power Query, you could use 'Remove Duplicates'.

2.You also could use DISTINCT function to remove duplicates.

The original table

The new table by using DISTINCT

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I'm just learning Power BI.

Where do I locate the Power Query screen you are displaying?

Also, if I choose 'Remove Duplicates' how do I ensure the duplicates being removed are the oldest versions and not the most recent. The four boxes in the lower left are the qualifications forwhich old duplicates need to be removed and only the most recent version of each of the four qualification should be counted in the Power BI query.

[v-stephen-msft]

Hi @Anonymous ,

You can open Power Query by clicking Transform data from Home in Power BI Desktop.

However, based on my test, removing duplicats is to keep the first row of duplicate values. I think you may want to abandon this method.

Now I found another method that does not need to remove duplicate values.

Sample data

Put the Column1, Column2 and Date into the visual. Here I use table visual to demonstrate.

Click the button pointed by the red arrow and select First. This is to remove duplicats.

Click the button pointed by the red arrow and select Earliest. This is to filter the earliest date.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

How do I get the earliest to appear in the Fitler choices?

Do I have to change the name of the column? Or, do I have to create a new column?

Can I not simply filter out all but the most recent based on the existing data; returning ONLY the most current of each item based on the current information?

[v-stephen-msft]

Hi @Anonymous ,

Sorry to disturb you...

But did I answer your question ? Please mark my reply as solution. Thank you very much.

Best Regards,
Stephen Tao

[Anonymous]

I am unsure. I am not very familiar with Power BI at this point. I'm trying to walk my way through and built this screen trying to under the in and outs of it all. Consequently, terms and processes don't sound familiar to me, complicating the matter.

It is unclear to me how to achieve what you are suggesting. I have confirmed the data type of the column is question, 'DteofCQ' is date.  As it pertains to creating a 'measure', this is not familir to me. Can you please elaborate on where an how this is achieved?

[v-stephen-msft]

Hi @Anonymous ,

For beginners, I usually recommend them to look at the official documentation. I think you will also benefit from it.

Power BI documentation

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-stephen-msft]

Hi @Anonymous ,

To get the earliest, the data type of the column must be a date type.

Or create a measure to get the earliest date with MIN function.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.