topic Re: Create list of values in order to parse it into a filter with IN in DAX Commands and Tips

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

ThomasSan — Wed, 11 Jan 2023 15:30:43 GMT

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!

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

amitchandak — Wed, 11 Jan 2023 15:43:30 GMT

ThomasSan , Something like this

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

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

johnt75 — Wed, 11 Jan 2023 15:48:32 GMT

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.

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

ThomasSan — Thu, 12 Jan 2023 08:06:30 GMT

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