Filter MAX date in CALCULATETABLE

[NickProp28]

Dear Community,

I had use CALCULATETABLE to filter from main table and create a new table. 

But after new table formed, I realized there have duplicate issues. 
To workaround this issue, I would like to choose the MAX date of ETD if there have duplicate consolnumber.

This is my CALCULATETABLE dax,

SummRoute =

CALCULATETABLE(

ConsolLegs,

FILTER(ConsolLegs,

ConsolLegs[IsDomestic] = "N" && ConsolLegs[Rank] = 1 )

)
Question is how can I filter the Max DATE ? I tried add on MAX(ConsolLegs[ETD]  before the filter, but it result fail.
Or I should use SUMMARIZE instead of CALCULATETABLE?
Any helps would appreciated. Thanks

[AlB · Accepted solution]

@NickProp28 

Try this then

New table2 = 
FILTER (
    FILTER ( Route, Route[IsDomestic] = "N" && Route[Rank] = 1 ),
    VAR max_ =
        CALCULATE (
            MAX ( Route[ETD] ),
            ALLEXCEPT ( Route, Route[ConsolNumber] ), Route[IsDomestic] = "N", Route[Rank] = 1
        )
    RETURN
        Route[ETD] = max_
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@NickProp28 

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@NickProp28 

Try this then

New table2 = 
FILTER (
    FILTER ( Route, Route[IsDomestic] = "N" && Route[Rank] = 1 ),
    VAR max_ =
        CALCULATE (
            MAX ( Route[ETD] ),
            ALLEXCEPT ( Route, Route[ConsolNumber] ), Route[IsDomestic] = "N", Route[Rank] = 1
        )
    RETURN
        Route[ETD] = max_
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@NickProp28 , did the above one worked. if not

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[NickProp28]

Dear @amitchandak ,

https://ufile.io/qf1fouxz
Kindly take a look on this PBIX.
Consolnumber C001490 has duplicated. And I only want to choose the MAX(Consolnumber[ETD]) 
Appreciated for the help.

[amitchandak]

@NickProp28 , Try if this can help

NewTable = var _max = maxx(Route, Route[ETD])
return 
filter(CALCULATETABLE(
    ADDCOLUMNS(Route, "Rank1", rankx(filter(Route, [ConsolNumber] =EARLIER([ConsolNumber])),[ETD])),
FILTER((Route),
Route[IsDomestic] = "N" && Route[Rank] = 1  )
),[Rank1]=1)

[AlB]

Hi @NickProp28 

Try creating a new calculated table:

New table =
FILTER (
    FILTER ( ConsolLegs, ConsolLegs[IsDomestic] = "N" && ConsolLegs[Rank] = 1 ),
    VAR max_ =
        CALCULATE (
            MAX ( ConsolLegs[ETD] ),
            ALLEXCEPT ( ConsolLegs, ConsolLegs[ConsolNumber] )
        )
    RETURN
        ConsolLegs[ETD] = max_
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[NickProp28]

Dear @AlB ,

Thanks for your time and help.
I follow your code, but it return nothing. 

[AlB]

@NickProp28 

That's weird. It should certainly return something. 

You seem to have filters active in two columns in the pic you show. Make sure to clear those.

Otherwise share some sample data or best the pbix itself

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@NickProp28 , Summarize will delete any duplicates ,

You should try like example

new table =

var _max = MAXX(ConsolLegs,ConsolLegs[ETD]  )

return

calculatetable(ConsolLegs, filter(ConsolLegs, ConsolLegs,ConsolLegs[ETD]  =_max)) //add other filters ot use summarize

[NickProp28]

Dear @amitchandak ,

Thanks for the time and guide, but I not sure if I follow your code correctly.. 
Kindly advice.