Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
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
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
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
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
@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.
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)
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
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
@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
Dear @amitchandak ,
Thanks for the time and guide, but I not sure if I follow your code correctly..
Kindly advice.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |