Power Pivot Problems

[Anonymous]

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?

[Anonymous]

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.

[dufoq3]

Provide sample data in usable format please. If you don't know how - read note below. Don't forget to provide also expected result based on sample data.