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