Multiple filter

[renatof]

Hi,

I have a situation like on the picture (WHAT I HAVE) 

So multiple values in each column. I need to find last address which was entered (last entry no.)

When I type formula 

Adrese = CALCULATETABLE(Table1;FILTER(Table1;Table1[Type] = "Address" && max(Table1[Entry_No])))

I got the red table which is not correct, and what I need is in the green table. 

I tried with calculated table, but the measure will be also fine, just to get last value based on type address and last entry no. 

Thanks,

Renato. 

[v-piga-msft]

Hi @renatof,

By my test, you could have a try with this formula below.

Table 2 =
TOPN (
    CALCULATE (
        COUNT ( 'Table1'[Person_No] ),
        FILTER ( 'Table1', 'Table1'[Entry_No] = MAX ( 'Table1'[Entry_No] ) )
    ),
    'Table1',
    'Table1'[Entry_No], DESC
)

Then you will get the output.

Hope this can help you!

Best Regards,

Cherry

[renatof]

Hi,

this could work but column type I have multiple types so one more filter which is related to Table1[Type] = "Address" + last entry no. 

[v-piga-msft · Accepted solution]

Hi @renatof,

If you only to get the max Entry_No when the type is Address, please try this formula again.

Table 3 =
TOPN (
    1,
    SUMMARIZE (
        'Table1',
        Table1[Person_No],
        Table1[Value],
        'Table1'[Type],
        "max", CALCULATE (
            MAX ( 'Table1'[Entry_No] ),
            FILTER ( 'Table1', 'Table1'[Type] = "Address" )
        )
    ),
    [max], DESC
)

Best Regards,

Cherry

[renatof]

Thank you. This is solution 🙂

[Pulkit]

Why don't you use the visual level filter? Select show only TOP N category and sort it on [Entory_No]

[Aree]

Have you tried 

Adrese = CALCULATETABLE(Table1;FILTER(Table1;Table1[Type] = "Address" ), Table1.Entry_No = max(Table1[Entry_No]))

Basically i split the two fliters. Without test data i cant simulate to be certain. 

[renatof]

Yes I tried with Adrese = CALCULATETABLE(Table1;FILTER(Table1;Table1[Type] = "Address");Table1[Entry_No] = MAX(Table1[Entry_No]))

and I got error A Function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.