Create list of values in order to parse it into a filter with IN

[ThomasSan]

Hi everyone,

I have a table that has the following structure:

Letter	Letter Group
A	1
B	1
C	1
D	2
E	2
F	2
G	3
H	3
I	3

I would like to get a list of letters by chosing a letter group. This list would then be needed in a FILTER segment of a CALCULATE function. 
An example would be as follows:

CALCULATE(
    sum('Table'[Values]),
    FILTER(
        'Table',
        'Table'[Letter] in {listofletters}
    )
)

where listofletters is a list of all letters from letter group 2 (so D, E, F)

As you can see, I am attempting to sum up all values of table 'Table' that are associated with the list of letters from Letter Group 2. My questions are the following:

1) Is my application of the FILTER function correct i.e. is it possible to parse in a list of values in the FILTER function so CALCULATE is only summing up values associated with the list of letters?

2) What is the proper DAX command in order to obtain such a list of letters?

Thank you for your help in advance!

[johnt75]

You can get the values by using the VALUES function, so to get all the letters from letter group 2 you could use

VAR LettersFromGroup2 = CALCULATETABLE( VALUES( 'Table'[Letter]), 'Table'[Letter Group] = 2 )

but you don't need to do that. You can use the letter group column itself as a filter, so you could write your measure as 

Letter group 2 sum =
CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Letter Group] = 2 )

If the column you are filtering on is in the same table as the values you wish to sum then you may need to use REMOVEFILTERS if other columns from the same table are in the visual.

[amitchandak · Accepted solution]

@ThomasSan , Something like this

CALCULATE(
sum('Table'[Values]),
FILTER(
'Table',
'Table'[Letter] in summarize(filter(Table, Table[Letter Group]=2), Table[Letter])
)
)

[ThomasSan]

That was exactly what I was looking for. Thank you @amitchandak !