Hi there,
sorry, I'm having a really hard time putting in words what I need, so here's my raw data:
[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.
I haven't tried this, so you'll need to experiment, but I'd probably try something along the lines of:
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.
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! :)
You could just extend it for each value that you want to check for:
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:
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...
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.