Eliminate Duplicates

[Anonymous]

I'm pulling data from an SP list. The list is made up of qualifications and certifications (1989 - 2020). Some of the qualifications repeat, as the employee needs to requalify periodically (1 per yr, every 2 yrs, every 3 yrs), depending.

In Power Apps I have a condition that eliminates the duplication and only displays the most recent of those items that appear more than once on the list. 

Is there way to implement the same conditions to the data I'm pulling into my BI screen?

[v-jayw-msft]

Hi @Anonymous ,

Can you show some sample data and expected result to us if you don't have any Confidential Information?

Best Regards,

Jay

[Anonymous]

Here is another version of the slide I provided at the top.

The desire is to ensure all data section totals ONLY reflect the most recent/active/noncompliant of any document names that repeat in the SP List for each Member.

There was a recommedation that I try Fitler/First, but that was later determined to not be the answer, and that method only reconciles the 'first' not duplicates.

[ChrisMendoza]

I guess I do not understand the problem well enough. This is actully the picture I meant to upload.

[Anonymous]

Based on what I believe your are formula is suppose to do - or at least what I need your formula to do, the table on the bottom right should have three title, one of each, and that one should be the most recent of the two.

[ChrisMendoza]

@Anonymous - You mention using Power Apps, what is your function/formula that you are using to filter your Sharepoint List there?

[Anonymous]

Here is the formula you requested. As written it only returns the most recent of those listed; those listed are the qualifications that repeat over years; however, for the Power BI screen the counts should only reflect the most recent of those qualifications, for all others will be 'Noncompliant' as a result of the date they were issued. Only one of each, whether compliant or not, should appear in the table and in the counts.

I'm using the following on screen Visible.

ClearCollect(
MemberCertificationandQualificationCollectionMostRecentOnly,
{Result: "Live Fire Refresher", Days: 720},
{Result: "Fitness Test", Days: 365},
{Result: "Self-Contained Breathing Apparatus Fit Test", Days: 365},
{Result: "Medical", Days: 1095}
);

[ChrisMendoza]

So ONVISIBLE you create a table/collection:

Result	Days
Live Fire Refresher	720
Fitness Test	365
Self-Contained Breathing Apparatus Fit Test	365
Medical	1095

and then you do what? Do you compare TODAY( ) to 'Sharepoint List'[LastCertificationDate]? How are you filtering the Sharepoint List in Power Apps? Knowing that will help us help you.

[Anonymous]

The Gallery filter is:

Filter(ForAll(Distinct(MemberCertificationandQualificationCollection,CQTitle),LookUp(Sort(Filter(MemberCertsandQualsList,(DteofCQ>=DatePicker1.SelectedDate)&&(DteofCQ<=DatePicker2.SelectedDate)&&FullNm=ECMemberSearchDD.Selected.FullNm),DteofCQ,Descending),CQTitle=Result)),!IsBlank(CQTitle))

[ChrisMendoza]

Something like Adding a RELATED COLUMN

relatedDaysColumn = RELATED('Table'[Days])

and a Measure

Measure = 
VAR __temp = DATEDIFF(SELECTEDVALUE(Table2[LastQualification]), TODAY(),DAY)
RETURN
CALCULATE(
    COUNT(Table2[employee]),
    FILTER(Table2,Table2[relatedDaysColumn] <= __temp
    )
)

Providing data would help us in coming up with a solution.

[Anonymous]

There is already a 'DaystoRenewal' column in the List that looks just like what you created.

[mahoneypat]

Pull down the arrow on Count of Park and choose distinct count instead.  Use a column there that has the values you want the distinct count of (e.g., name or personID).  Later, much more is possible when you start learning to write and use measures instead of the default aggregations.

Regards,

Pat

[Anonymous]

I'm sure I have much to learn and enjoy. I've heard it from the Power Apps geek, the SharePoint geek, now a magnanimous Power BI person. 😁

I opened the 'Count of Park', couldn't find a 'distinct' option. Also, my concern is not a repretition at the park level or of the park names themselves, it is with the CQTitles. For example the system is storing all Fitness Test; a member has to pass a Fitness Test every year. I only want the BI graph to query and return data on the Member's most recent, not all of their Fitness Test. Here is in opened CQTitle. Should I make your recommended change here? If yes, what/how? Thanks!

[Syndicate_Admin]

Back to you 'pull down and pick" suggestion; won't that simply ONLY show me those that I've newly selected? Meaning, if any of the newly selected are repeats of the same CQTitle, just from an early year, they will still show up in the query based on this action, yes? Meaning I will still see every 'Fitness Test' associated with a Member if I only chose 'Fitness Test' as the "pull down" filter choice, yes? If yes, this is what I am trying to avoid. I DO NOT want the Region (PWR, NER, SER, etc.) Totals to include ANY duplicate 'qualifications' - only the most recent qualifications and all of the certifications (certifications are only issued once, so there would natually only be an impact of one on the total; whereas qualifications, if duplicates are not filtered out would have an impact of multiples.

All CQTitles of "Fitness Test", "Medical" and "Self-Contained Breathing Apparatus Fit Test" should only be counted once/the most recent; all other similar qualifications from earlier years should not factor in the overall "Compliance" totals?

I hope this makes sense.

[mahoneypat]

Please share your measure expression (or your selected aggregations in the visuals).  The visuals you've shown don't seem to have a Date axis/component.  I suspect you just need to use distinct count or DISTINCT() or VALUES() in your measures.

Regards,

Pat

[Anonymous]

I'm new to PowerBI and I'm not very familiar with many of the technical terms. I'm making it up as I go along. I hope this is what you requested.

[Anonymous]

I'm not measuring anything by date in PowerBI.

Its just a display counts performed in Power Apps or Sharepoint.