Show the row in gallery if the value is less than 1

[rohitkushwaha]

I need help with the below code. So I have to show the data in gallery if the value in the column is smaller than 1.

Previously the data was showing only for the blank rows in the column but now I need modification to show value less than 1, so in the first row the value is 0.5 so I need that data in gallery.

Piece of code to be modified(I guess):

// 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)
                        )

Column:

Full code:

// 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
            )
        )
    )
)

[scalca]

try this:

If(ThisItem.'YourValueColumn' < 1, ThisItem.'YourValueColumn', Blank())

if you only want to show the value if it's less than 1 

in case you want to remove the whole row then you'll have to use Filter() function

[mmbr1606 · Accepted solution]

hey @scalca 

try this code:

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 column where the value is less than 1
            Last( Filter( 

                // Create a table of employees with their last week column values less than 1
                // This list will have multiple entries for each employee, one for each column with value less than 1

                // 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 value is less than 1
                            ForAll( Sequence( ThisWeekNum +1, 0) As ColNum,
                                With({
                                    // Switch the column to get the value
                                    ColValue: Switch(
                                        ColNum.Value,
                                        0, "Not Blank Failsafe", // Preserving your original failsafe
                                        1, EWD1.One,
                                        2, EWD1.Two,
                                        3, EWD1.Three,
                                        4, EWD1.Four,
                                        5, EWD1.Five
                                    )
                                },
                                    // If column is not blank and value is less than 1, return data
                                    If( !IsBlank( ColValue) && ColValue < 1,
                                        { NotBlankCol: ColNum.Value}
                                    )
                                )
                            ),

                            // Remove records where column value is blank or >= 1
                            !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 weeks with values less than 1
            AddColumns(
                EmployeeData,
                // Add column to calculate number weeks with value less than 1
                "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 weeks up to this week where value is less than 1
                        Filter( WeekNumberLookUpTable, Week > ThisWeekNum -Unallocated && Week <= ThisWeekNum),
                        "Days", DateDiff( StartDate, EndDate) +1
                    ), 

                    // Sum the days column
                    Days
                ),

                // Sum could be blank if no weeks with value less than 1
                // Put a 0 instead
                0
            )
        )
    )
)

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings

[rohitkushwaha · Accepted solution]

Hi @mmbr1606 

Have you changed code in multiple place or just the below once? 

If( !IsBlank( ColValue) && ColValue < 1,
    { NotBlankCol: ColNum.Value}
)

 If you've changed only the above piece of code, I'm getting Invalid argument type error.

[mmbr1606]

try to put Value(ColValue)<1

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings

[rohitkushwaha]

@mmbr1606 It worked, Thank you!

[mmbr1606]

good to see it worked.