Forum Discussion
Eliminate Duplicates
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.
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))
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.
There is already a 'DaystoRenewal' column in the List that looks just like what you created.