Forum Discussion
How to filter ID values containing specific values
This is a follow-up to a different question I had, which was solved by @PaulDBrown.
I'm trying to find all ID values containing specific values. Example given here:
In this example I need to find all ID values that contain either "A" or "D" then assign a "1" to all rows with one of those ID values, regardless if the row contains "A", "D" or both.
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 )
8 Replies
EVG-Questions , create a measure like
measure =
var _cnt = calculate(distinctcount(Table[cat1]) , filter(allselected(Table), Table[ID] =max(Table[ID])))
var _cnt2 = calculate(distinctcount(Table[cat1]) , filter(allselected(Table), Table[ID] =max(Table[ID]) && Table[cat1] in {"A", "D"}))
return
if(_cnt=2 && _cnt=_cnt2 ,1,0)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 )
