Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
@ThomasSan , Something like this
CALCULATE(
sum('Table'[Values]),
FILTER(
'Table',
'Table'[Letter] in summarize(filter(Table, Table[Letter Group]=2), Table[Letter])
)
)
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.
@ThomasSan , Something like this
CALCULATE(
sum('Table'[Values]),
FILTER(
'Table',
'Table'[Letter] in summarize(filter(Table, Table[Letter Group]=2), Table[Letter])
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |