Problem using all in a formula

[Anonymous]

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 =

CALCULATE(min(follow_criticity_activity[date_end_criticity]),
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

[Anonymous]

Hi all,

Does someone have an idea ?

Many thanks !

[Anonymous]

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

[Anonymous]

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