cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NickProp28
Post Partisan
Post Partisan

Filter MAX date in CALCULATETABLE

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.

NickProp28_1-1608539327861.png


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


2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

@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 

 

SU18_powerbi_badge

View solution in original post

AlB
Super User
Super User

@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 

 

SU18_powerbi_badge

View solution in original post

10 REPLIES 10
AlB
Super User
Super User

@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 

 

SU18_powerbi_badge

View solution in original post

AlB
Super User
Super User

@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 

 

SU18_powerbi_badge

View solution in original post

amitchandak
Super User
Super User

@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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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.

@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)
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
AlB
Super User
Super User

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 

 

SU18_powerbi_badge

 

Dear @AlB ,

 

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

NickProp28_0-1608542941971.png

 

@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 

 

SU18_powerbi_badge

 

amitchandak
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Dear @amitchandak ,

 

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

NickProp28_1-1608543334661.png

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!