hi,
I'd like to write a measure to count the items in TableB that are in TableA, to do this I've got as far as
The problem is I get an extra row for all non-matching items, I thought using DISTINCT rather than VALUES might fix this, but it hasn't.
Suggestions please also if there are better ways of achieving this let me know.
Richard.
I'd like to write a measure to count the items in TableB that are in TableA, to do this I've got as far as
Excel Formula:
CountMatchingM :=
VAR CountT1 =
COUNTROWS ( Table1 )
VAR Table2Items =
DISTINCT ( Table2[ItemTwo] )
VAR anser1 =
CALCULATE ( CountT1, FILTER ( ALL ( Table1 ), Table1[Items] = Table2Items ) )
RETURN
anser1
The problem is I get an extra row for all non-matching items, I thought using DISTINCT rather than VALUES might fix this, but it hasn't.
Suggestions please also if there are better ways of achieving this let me know.
Richard.