Need help creating a measure

[eiriksan]

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!

[Zubair_Muhammad · Accepted solution]

@eiriksan

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
    )

[eiriksan]

Thanks a lot! The first solution worked well and solved my problem. Much appreciated!

[Zubair_Muhammad]

@eiriksan

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