Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I'M A STRUGGLING STUDENT AND IM ABOUT TO THROW MY LAPTOP. I'm currently using power pivot to generate a pivot table. The data has mock flight data with different routes and months of each flight. I am trying to create a column that calculates the most flied month (mode) for each route. I figured out how to calculate the mode using DAX commands but I am unable to filter it to each specific route because it is a value of a separate column. Is there any method that can group the different routes together?
Hi @Anonymous ,
@dufoq3 Thanks for your concern about this case!
Here is my sample data:
You can achieve this in Power Query, put this M function into the Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUo0BBKGSrE6RHON8HKNEVwjIGGCl2uKyjUDc52ArCSQUeZ4uRYILkivJSlcQwME3xjEN0TjG2HyYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [flight = _t, route = _t, month = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"flight", type text}, {"route", type text}, {"month", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"flight", "route", "month"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Grouped Rows Max" = Table.Group(#"Grouped Rows", {"flight", "route"}, {{"MaxCount", each List.Max([Count]), type number}}),
#"Merged" = Table.Join(#"Grouped Rows", {"flight", "route", "Count"}, #"Grouped Rows Max", {"flight", "route", "MaxCount"})
in
#"Merged"
And the final output is as below:
If you must need DAX for calculated column, you can try this:
First add a Count column:
Count =
CALCULATE(
COUNT('Table'[month]),
ALLEXCEPT('Table', 'Table'[route], 'Table'[month])
)
Then use this DAX to create a calculated column:
MAX =
VAR _max = CALCULATE(MAX('Table'[Count]),ALLEXCEPT('Table','Table'[route]))
VAR _route = 'Table'[route]
VAR result = CALCULATE(MIN('Table'[month]),FILTER(ALLEXCEPT('Table', 'Table'[route]), 'Table'[Count] = _max))
RETURN
result
And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.