Second Filter in measure doesn't work if dynamic

[Anonymous]

Hello,

I'm trying to use two filters in a measure. My tables look like this:

Table1

ColumnA|ColumnB|ColumnC

Table2

ColumnA

Measure = CALCULATE(SUM(Table1[ColumnC]), FILTER(TableA, TableA[ColumnA] IN ALLSELECTED(Table2[ColumnA], FILTER(TableA, TableA[ColumnB] = CALCULATE(MAX(Table1[ColumnB]), FILTER(Table1, Table1[ColumnA] IN ALLSELECTED(Table2[ColumnA]))))

So what I am basically trying to achieve is to first filter Table1 by the selected values in Table2 (that works) and then also filter by the MAX value in ColumnB in TableA again depending on what the max value is for the selected values in Table2 in ColumnB of Table1 (that doesn't work). 

Now to begin with I'm aware that this is probably not a very elegant way to do this but I didn't find any better way so far. What I don't understand is why it does work if I replace the second Filter condition simply by a number, so let's say 2 would be the highest number in ColumnB of Table1 after being filtered by the first filter, this works:  

Measure = CALCULATE(SUM(Table1[ColumnC]), FILTER(TableA, TableA[ColumnA] IN ALLSELECTED(Table2[ColumnA], FILTER(TableA, TableA[ColumnB] = 2))

How can I replace the '2' by a dynamic filter?

Thanks for any help!

[Anonymous]

Hi @MFelix , thanks for your response! ColumnB in Table1 is not related to Column1 in Table2. 

It's a fairly complex dataset I'm dealing with but I'm trying to formulate some mockup-data - this would be Table1: 

ColumnA	ColumnB	ColumnC
a	1	10
b	2	20
c	3	5
a	2	15
b	2	10
a	2	3
d	1	8

  While Table2 would be simply:

ColumnA

a

b

c

d

So as an example, if I select now "a" from Table2, my measure should sum up alle the values for "a" in Table1 where ColumnB has the highest value (in case of "a" 2 is the highest value) --> so the result should be 15 +3 = 18 (which it is only as long as I don't try to calculate the highest value in ColumnB but simply set FILTER(TableA, TableA[ColumnB] = 2 - as soon as I use the second filter dynamically the measure simply ignores the filter it seems).

I can't describe it any better I think😟

[MFelix · Accepted solution]

Hi @Anonymous ,

If I understood corretly you want the sum of the all the values selected on the second table and then get the maximum value and sum all the values that match those maximum.

Try the following measure:

TotalSumWithMax = 
var temp_table = FILTER(Table1, Table1[ColumnA] in VALUES(Table2[ColumnA]))
var maximumValue = MAXX(temp_table, Table1[ColumnB])
return
SUMX(FILTER(temp_table, Table1[ColumnB] = maximumValue), Table1[ColumnC])

Check PBIX attach.

[Anonymous]

Hello @MFelix, thank you very much for your help! With some slight modifications it works now👍

[MFelix]

Hi @Anonymous ,

What is the value you want to have dynamic in the B column is it based also in the Column A of table 2?

How are you setting up the values? 

Measures are based in context so adding or not the values in your visualizations or measures can change the expected result.

Can you share what is the result you are trying to achieve? if you can share some mockup data and expected result would be easier to answer you.