Forum Discussion
Problem using all in a formula
Hi all,
I have troubles calculating a progress state at a certain date when another date is selected in a segment.
This is my model :
My data set can be like this :
Table follow_criticity_activity :
| id_activity | lib_criticity | date_end_criticity |
| 1 | critic this month to do within 1 month | 2023/08/31 |
| 1 | critic last month to do within 1 month | 2023/07/31 |
table activity :
| id_activity |
| 1 |
planif_activity:
| id_activity | date_alim | progress | date_end |
| 1 | 2023/07/28 | not started | 2023/07/28 |
2023/07/29 | not started | 2023/07/28 | |
| 1 | 2023/07/30 | in progress | 2023/07/28 |
| 1 | 2023/07/31 | in progress | 2023/07/28 |
| 1 | 2023/08/01 | finished | 2023/08/01 |
ref_alim :
| date_alim | lib_date |
| 2023/07/28 | 2023/07/28 |
2023/07/28 | 2023/07/28 |
| 2023/07/28 | 2023/07/28 |
| 2023/07/31 | end of previous month |
| 2023/08/01 | today |
I have a matrix containing planif_activity table's rows, and two segments can act on it : ref_alim.lib_date (ordered by ref_alim.date_alim) and follow_criticity_activity.lib_criticity.
I want to display in the matrix a progress state (if activity is finished then 1 else 0) based on planif_activity.progress and planif_activity.date_end at the date selected on segment ref_alim.lib_date (no problem on that), and at the follow_criticity_activity.date_end_criticty wich depend on the second segment follow_criticity_activity.lib_criticity regardless of the first segment (I can't get it out).
For exemple, if ref_alim.lib_date='today' and follow_criticity_activity.lib_criticity='critic last month to do within 1 month', I would like to have on my matrix :
| planif_activity.id_activity | planif_activity.date_end | progress state at ref_alim.lib_date='today' | progress state at follow_criticity_activity.date_end_criticty where follow_criticity_activity.lib_criticity='critic last month to do within 1 month' |
| 1 | 2023/08/01 | 1 | 0 |
I tried this measure to determined if an activity is finished (=1) or not (=0) at the follow_criticity_activity.date_end_criticty :
finished_activity =
var msr_end_date =
filter(follow_criticity_activity, follow_criticity_activity[lib_criticity]=SELECTEDVALUE(follow_criticity_activity[lib_criticity])))
return CALCULATE(Count(planif_activity[id_activity])
,FILTER(CALCULATETABLE(planif_activity, REMOVEFILTERS(ref_alim)),
planif_activity[progress])="FINISHED" && planif_activity[date_alim]=[msr_end_date ])
)
But I have two rows displayed instead of one :
| planif_activity.id_activity | planif_activity.date_end | progress state at ref_alim.lib_date='today' | progress state at follow_criticity_activity.date_end_criticty where follow_criticity_activity.lib_criticity='critic last month to do within 1 month' |
| 1 | 2023/08/01 | 1 | |
| 1 | 2023/07/28 | 0 |
Do you have an idea how to do that ?
Thank you in advance.
Alice
3 Replies
Hi Anonymous ,
The last two fields [progress state at ref_alim.lib_date='today'] and [progress state at follow_criticity_activity.date_end_criticty where follow_criticity_activity.lib_criticity='critic last month to do within 1 month'] are the count of activities? What's the calculation logic? Which conditions need to be fulfilled?
The conditions should be?
- planif_activity[progress])="FINISHED"
- follow_criticity_activity.lib_criticity='critic last month to do within 1 month'
- ref_alim.lib_date='today'
Best Regards
Hi,
Thank you for your reply.
Here is my pbix file : https://we.tl/t-HWlyzNaBh0
I want to have the progress of this configuration :Into this matrix at a different configuration :
To have this result :
Thank you in advance for your help.
Alice