Find values for dimension where another dimension exists in combination

[hespora]

Hi there,


sorry, I'm having a really hard time putting in words what I need, so here's my raw data:

Code Select Expand


[ColumnA]   [ColumnB]
A            X
A            Y
A            Z
B            X
B            Z
B            AA
C            X
C            Y
D            Y
D            Z
D            AA
D            AB

What I'm looking for is those values for column A where the combination of values X and Y in column B exists, so in this case values A and C.

What I can do of course is a query join: Create a query1 with a filter of [ColumnB]='X' and a query2 with [ColumnB]='Y', and then inner join those on [ColumnA]. However, I'm looking for ways to avoid a join; for one due to runtime improvement, and secondly, because then I'm hardcoded to finding combinations of exactly 2 values. In the future, I might need the same report for combinations of 3 or 4 different values for column B.

Any ideas on how I might achieve this?

/edit: on relational, and neither of these columns are measures.

[BigChris]

I haven't tried this, so you'll need to experiment, but I'd probably try something along the lines of:

Code Select Expand

maximum(if([Column B] = 'X') then (1) else (0) for [Column A]) * maximum(if([Column B] = 'Y') then (1) else (0) for [Column A])

And then filter for your calculation being equal to 1.

[hespora]

yup, works like a charm. Gotta play around with how I'm going to expand that for a varying number of values to check for, but great for a start. Thank you! :)

[BigChris]

You could just extend it for each value that you want to check for:

Code Select Expand


maximum(if([Column B] = 'X') then (1) else (0) for [Column A])
* maximum(if([Column B] = 'Y') then (1) else (0) for [Column A])
* maximum(if([Column B] = 'Z') then (1) else (0) for [Column A])
etc.


If you wanted to be really cute, you could put parameters in:

Code Select Expand


if(?FirstCheck? is not missing) then (maximum(if[Column B] = ?FirstCheck?) then (1) else (0) for [Column A])
* if(?SecondCheck? is not missing) then (maximum(if[Column B] = ?SecondCheck?) then (1) else (0) for [Column A])
etc.


Again, I haven't tested any of that, but it looks like it should work...

[hespora]

Yea, I was thinking exactly along those lines. The outer if probably needs an else (1) to work as it otherwise would return null, but other than that looks great.