Set analysis with intersection of possible values sets - Issue about dimension equality

[Alex9]

Hello,

As mentioned in the title, I'm asking for your help about some issues in a set analysis with intersection of possible values sets.

Here are my input data:

Mission	Date	Origin	Destination
M1	01/02/2021	A	B
M1	01/02/2021	B	C
M1	01/02/2021	C	D
M1	01/02/2021	D	E
M2	01/02/2021	B	C
M2	01/02/2021	C	D
M2	01/02/2021	D	E
M3	01/02/2021	A	B
M3	01/02/2021	B	C
M3	06/02/2021	C	D
M3	06/02/2021	D	E
M3	06/02/2021	E	F

And I would like to determine in a set analysis the Missions by Date which have passed through C and E Destination (at the same Date) in order to get this:

Date	Mission
01/02/2021	M1
01/02/2021	M2

I tried to use the following set analysis in the Mission expression:

{<Mission=P({<Destination={"C"}>} Mission)*P({<Destination={"E"}>} Mission)>}

But I got that:

Date	Mission
01/02/2021	M1
01/02/2021	M2
01/02/2021	M3
06/02/2021	M3

M3 passed through C and E destination but not the same date that's why I want to exclude it.

It seems that the dimension Date in the table is not taken into account in the set analysis Mission part.

Do you know if there is a way to make sure about the Date dimension equality in the two possible values sets ?

Thanks in advance for you help,

Alex.

[agigliotti · Accepted solution]

Hi @Alex9 ,

You could use the below expression for your measure:

=sum( if( count( {< Destination = {'C','E'} >} >} distinct Destination ) = 2, 1, 0 ) )

with Mission and Date as dimensions plus enabling "Suppress Zero-Values" option under "Presentation".

I hope it can help.

Best Regards

[Alex9]

Hello @agigliotti,

It works perfectly !

Thank you ,

Alex