Nested Filters in Calculated column

[Dimitris_Kats]

Dear members hi.

I really need your help!

I have a calculated column where I am retrieving the max ID from another table based on specific parameters.

My code is as follows:

Calculate(Max('Table1'[ID]), Filter(

               Filter (

               Filter(

                  Table1[countryid]=table2[countryid]),

Table1[cityid]=table2[cityid]),

Table1[start date]<=table2[date] &&

Table1[end date]>table2[date] ),

Table1 [flag]=false)

)

I need a way either to optimize this calculated column or reproduce this code as measure .

Any ideas on how to do it??

Thank you very much in advance 🙂

[amustafa]

Hi @Dimitris_Kats ,

You can try this as a measure:

MaxIDMeasure =
CALCULATE(
MAX('Table1'[ID]),
FILTER(
'Table1',
'Table1'[countryid] = MAX('table2'[countryid]) &&
'Table1'[cityid] = MAX('table2'[cityid]) &&
'Table1'[start date] <= MAX('table2'[date]) &&
'Table1'[end date] > MAX('table2'[date]) &&
'Table1'[flag] = FALSE()
)
)

[Dimitris_Kats]

Hello @amustafa 

Thank you very much for replying to me.

I have already tried this measure. Unfortunately it's loading forever and fails 😞

[amustafa · Accepted solution]

Sorry to hear that. Here's a different variation to reduce the number of rows scaned.

MaxIDMeasure =
VAR MaxCountryID = MAX('table2'[countryid])
VAR MaxCityID = MAX('table2'[cityid])
VAR MaxDate = MAX('table2'[date])

RETURN
CALCULATE(
MAX('Table1'[ID]),
'Table1'[countryid] = MaxCountryID,
'Table1'[cityid] = MaxCityID,
'Table1'[start date] <= MaxDate,
'Table1'[end date] > MaxDate,
'Table1'[flag] = FALSE()
)