Group by ID and subID and check if two values occur togheter

[Anonymous]

Hi,

I have a case where I wan't to find the combination of ID and Idref where Message and Search occurs together. I'm not sure how I can create the column "Contains Message & Search". It should group by ID and Idref and check if both Message and Search occurs. If it does, then for all rows for the combination of ID and Idref then return 1, otherwise just return 0.
I've tried some combinations of group by, summarize (but don't want to create a new table).. 
Would be great if someone can help me.

ID	Idref	type	Contains Message & Search
1	1	Message	0
1	1	Given	0
1	2	Search	0
1	2	Given	0
2	1	Message	1
2	1	Search	1
2	1	Given	1
3	1	Message	0
3	2	Message	0
3	2	Given	0
3	3	Message	1
3	3	Search	1
3	3	Given	1

Thanks!

[Jonvoge · Accepted solution]

Hi.

You can use the following measure:

Measure =

var HasMessage = CALCULATE(COUNTROWS('Table'), Filter(ALLEXCEPT('Table', 'Table'[Id], 'Table'[Idref]), 'Table'[type] = "Message"))

var HasSearch = CALCULATE(COUNTROWS('Table'), Filter(ALLEXCEPT('Table', 'Table'[Id], 'Table'[Idref]), 'Table'[type] = "Search"))

RETURN

If(HasMessage > 0 && HasSearch > 0, 1, 0)

_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data