Forum Discussion
Need help creating a measure
Hi,
I've been stuck trying to create a specific measure for days now and sorely need some help. Below is a simplified description of the problem:
I have two tables:
Table1: Table2:
Name Name Category
a a 1
b b 1
b c 2
c d 2
c
c
d
The two tables are linked by the column "Name".
I'm trying to create a visual (e.g. a matrix) that diplays the distinct values in Table1[Name] along with the count of occurences in Table1[Name] of the names that belong to the same category. Something like this:
Name Measure
a 3 ("a" belongs to category "1" and there are 3 occurences of "a" and "b" in Table1[Name])
b 3 ("b" belongs to category "1" and there are 3 occurences of "a" and "b" in Table1[Name])
c 4 ("c" belongs to category "2" and there are 4 occurences of "c" and "d" in Table1[Name])
d 4 ("d" belongs to category "2" and there are 4 occurences of "c" and "d" in Table1[Name])
What I need is a measure that responds in the way illustrated above. The measure must be dynamic and respond to other filters. Any help would be greatly appreciated!
You can use this MEASURE
Measure = VAR mycategory = LOOKUPVALUE ( Table2[Category], Table2[Name], SELECTEDVALUE ( Table1[Name ] ) ) RETURN COUNTX ( FILTER ( ALL ( Table1 ), RELATED ( Table2[Category] ) = mycategory ), 1 )
3 Replies
- Zubair_Muhammad
Community Champion
You can use this MEASURE
Measure = VAR mycategory = LOOKUPVALUE ( Table2[Category], Table2[Name], SELECTEDVALUE ( Table1[Name ] ) ) RETURN COUNTX ( FILTER ( ALL ( Table1 ), RELATED ( Table2[Category] ) = mycategory ), 1 )- Zubair_Muhammad
Another way could be to add a calculated column in Table1 to get the category
Category = RELATED(Table2[Category])
Then use this MEASURE
Measure 2 = VAR mycategory=selectedvalue(Table1[Category]) RETURN CALCULATE(count(Table1[Name ]),Filter(all(Table1),Table1[Category]=mycategory))
Please see file attached
Thanks a lot! The first solution worked well and solved my problem. Much appreciated!
