Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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,...
Need Help
ChrisMendoza's avatar
ChrisMendoza
Resident Rockstar
5 years ago

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    ChrisMendoza
    Resident Rockstar
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        ChrisMendoza
        Resident Rockstar
        5 years ago

        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.