I have only one value per date in the KPI table and I want the value to be displayed only once per date in the Tasks table, like the True KPI Value column in the Tasks table:
Hi @amin_jebeli you need to provide more verbosity to what you are trying to accomplish. Its not clear from what you wrote, nor from looking at the doc. Let me know and we can help!
I want to move the Value column in the KPI table to the Task table in the True KPI Value column, but for each date the value is recorded only once in the Task table, like the values in the True KPI Value column.
Using this formula, in the Wrong KPI Value column in the Task table, I entered the Value column in the KPI table in this column, but as it turns out, it is repeated for each date, while I want to repeat it only once for each date. Repeat like the True KPI Value column
KPI.Filter(person=thisRow.Person && [End Date]=thisRow.Date).Value
My question is what formula should I use for this topic?
What is the significance of
[Start Date] vs
Just so that people can see the start and end dates of their KPI when they see the KPI table, and there is only one date in the Task table where the end date of the task has been done, and this date must match the End date column in the KPI table.
OK for the purposes of this formula, the start date isn’t relevant?
OK @amin_jebeli you gave me enough information to solve the riddle! The key is to figure out if the row is the first one to have the KPI return. If so, the value should be the KPI’s value, otherwise zero.
I accomplish that by making a list of all the rows that return the
kpi, then see which of the group has the lowest
rowID() - the row with the lowest receives
[kpi].[value] whereas every other row returns 0. Let me know if you have any questions!
Thank You so much