if the Column3 value is 0 then remove all rows for that employee

[rohitkushwaha]

I need help to filter out gallery data where the value is 0 with condition that if the value is 0 for any employee then search if any other rows are present in galleries for that same employee and remove the data ( we can use emp ID to find other related columns of same employee).

For example:

For below table John and Kim have 0 value in Unallocated weeks/Unallocated days. Then remove both the data for John and Kim.
Note: If the Unallocated weeks is 0 then Unallocated days value will be 0, we can consider any one column)

Output:

[WarrenBelz · Accepted solution]

Hi @rohitkushwaha ,

Try this for a start

With(
   {
      _Data:
      Filter(
         DataSource,
         'Unallocated Weeks' = 0 ||
         'Unallocted Days' = 0
      )
   },
   Filter(
      DataSource,
      !('Emp ID' in _Data.'Emp ID')
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[rohitkushwaha]

I already have below code in my gallery, how can i merge your code into it? please help

// Input parameters
With({
    EmployeeData: EmployeeWeekData,
    WeekNumberLookUpTable: WeekNumbers,
    ThisWeekNum: 3
},
    With({ 
        // Create list to store employee non allocated weeks
        EmployeeAllocationLookUpTable:

        // For each employee, find the last non blank column up to specified week column
        ForAll( EmployeeData As EWD2,

            // Find the last non blank column
            Last( Filter( 

                // Create a table of employees with their last non-blank week column values
                // This list will have multiple entries for each employee, one for each non blank column

                // UnGroup to resolve nested data structure
                Ungroup(

                    // For each employee
                    ForAll( EmployeeData As EWD1, { 
                        ID: EWD1.ID,
                        Name: EWD1.Name,
                        Data: Filter(

                            // Search all weeks, recording any column which isn't blank
                            ForAll( Sequence( ThisWeekNum +1, 0) As ColNum,
                                With({
                                    // Switch the column to get the value
                                    ColValue: Switch(
                                        ColNum.Value,
                                        // Need this to ensure there is at least one non-blank value
                                        0, "Not Blank Failsafe",
                                        1, EWD1.One,
                                        2, EWD1.Two,
                                        3, EWD1.Three,
                                        4, EWD1.Four,
                                        5, EWD1.Five
                                    )
                                },
                                    // If column not blank, return data
                                    If( !IsBlank( ColValue),
                                        { NotBlankCol: ColNum.Value}
                                    )
                                )
                            ),

                            // Remove blanks - columns where value is blank will return blank record
                            !IsBlank( ThisRecord)
                        )
                    }),
                    "Data" // Merge nested data
                ), 
                ID = EWD2.ID
            ))
        )
    },
        // Add a column to count total unallocated days
        AddColumns(

            // Add a column to employee data to store number of blank weeks
            AddColumns(
                EmployeeData,
                // Add column to calculate number blank weeks
                "Unallocated", ThisWeekNum -LookUp(EmployeeAllocationLookUpTable, ID = EmployeeData[@ID]).NotBlankCol
            ),

            // Create new column total days
            "TotalDays", 

            // Use Coalesce to insert 0 instead of blank
            Coalesce( 

                // Total days is the sum of....
                Sum(

                    // Add number of days per week to week lookup table
                    AddColumns( 
                        // Use only unallocated weeks up to this week
                        Filter( WeekNumberLookUpTable, Week > ThisWeekNum -Unallocated && Week <= ThisWeekNum),
                        "Days", DateDiff( StartDate, EndDate) +1
                    ), 

                    // Sum the days column
                    Days
                ),

                // Sum could be blank if no unallocated days
                // Put a 0 instead
                0
            )
        )
    )
)

[WarrenBelz]

@rohitkushwaha ,

I suggest you either make a Collection with your code then use what I posted as the Gallery Items filtering the collection or make your entire code another WIth() statement and refer to the output where I have DataSource.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[rohitkushwaha]

After filtering using your code I need to filter "TotalDays" column to only show minimum value/row of Totaldays in gallery, could you please edit your code to show minimum value only or do I have to add another filter funtion?

[WarrenBelz]

@rohitkushwaha ,

Total days is not in the original question you posted (is this question now solved ?)

[rohitkushwaha]

Yes it is solved, I accepted as solution.

Need one more help,
After searching the data there are multiples records for the employee I just want to show the record with the minimum value in "Total weeks unallocated".

[WarrenBelz]

@rohitkushwaha ,

If you want to "add back" more fields, do the same the I have done with 'Associate Name' at the bottom.

With(
   {
      _Data1:
      Filter(
         DataSource,
         'Unallocated Weeks' = 0 ||
         'Unallocted Days' = 0
      )
   },
   With(
      {
         _Data2:
         Filter(
            DataSource,
            !('Emp ID' in _Data.'Emp ID')
         )
      },
      AddColumns(
         GroupBy(
            Sort(
               _Data2,
               'Total weeks unallocated'
            ),
            "Emp ID",
            "Grouped"
         ),
         "Weeks",
         First(Grouped).'Total weeks unallocated',
         "Associate",
         First(Grouped).'Associate Name'
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[rohitkushwaha]

I don’t want to add more fields, your code worked and row with 0 value and related rows is filtered out that’s done.

(Consider below problem as another problem I’m facing after filtering rows with 0 value)

Now there are long list of data after removing data with 0 value, now when I’m searching any Associate using emp name or emp id using the right panel (refer screenshot), I get multiple records for some employees so from that result I want to show only the minimum value record from “Total week unallocated”.

[WarrenBelz]

@rohitkushwaha ,

You might consider going "the other way" and filtering the code below by employee

With(
   {
      _Data1:
      Filter(
         DataSource,
         'Unallocated Weeks' = 0 ||
         'Unallocted Days' = 0
      )
   },
   With(
      {
         _Data2:
         Filter(
            DataSource,
            !('Emp ID' in _Data1.'Emp ID')
         )
      },
      Filter(
         AddColumns(
            GroupBy(
               Sort(
                  _Data2,
                  'Total weeks unallocated'
               ),
               "Emp ID",
               "Grouped"
            ),
            "Weeks",
            First(Grouped).'Total weeks unallocated',
            "Associate",
            First(Grouped).'Associate Name'
         ),
         'Associate' = YourDropdown.Selected.FieldName/Value
      )
   )      
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps