Forum Discussion
Eliminate Duplicates
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?
16 Replies
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
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
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!
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.
Anonymous - You mention using Power Apps, what is your function/formula that you are using to filter your Sharepoint List there?
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}
);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.
I guess I do not understand the problem well enough. This is actully the picture I meant to upload.
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.
