Forum Discussion
How to filter ID values containing specific values
Apologies...I was overthinking this. Try:
Only A or D = VAR _Table = CALCULATETABLE ( VALUES ( 'FactTable'[Cat1] ), ALLEXCEPT ( FactTable, FactTable[ID] ) ) VAR _Vals = { "A", "D" } VAR _Excpt = EXCEPT ( _Table, _Vals ) RETURN IF ( COUNTROWS ( _Excpt ) >= 1, 0, 1 )
This doesn't seem to be working for me. The measure only generates "0". Could you perhaps explain the thought behind your approach?
Try:
Countrows A or D =
VAR _Table =
CALCULATETABLE (
VALUES ( 'FactTable'[Cat1] ),
ALLEXCEPT ( FactTable, FactTable[ID] )
)
VAR _Vals = { "A", "D" }
VAR _INTS =
INTERSECT ( _Table, _Vals )
RETURN
IF ( COUNTROWS ( _INTS ) >= 1, 1, 0 )
Thanks for the response PaulDBrown!
This appears to be one step closer but doesn't quite solve it.
I'd like to get a list that exclusively consists of "A", "D" or both, whereas this creates a list of ID's that can also contain other values as long as there is an "A", "D" or both present.
Got it. I'm not in front of a PC now but try:
Countrows A or D =
VAR _Table =
CALCULATETABLE (
VALUES ( 'FactTable'[Cat1] ),
ALLEXCEPT ( FactTable, FactTable[ID] )
)
VAR _Vals = { "A", "D" }
VAR _INTS =
INTERSECT ( _Table, _Vals )
VAR _NotADCat =
EXCEPT ( _Table, _Vals )
VAR _Clean =
EXCEPT ( _INTS, _NotADCat )
RETURN
IF ( COUNTROWS ( _Clean ) >= 1, 1, 0 )I might be doing something wrong, but it seems that this code produces the same outcome as your previous measure.