Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Create a new Table to show the Minimum value based on multiple criteria

I'm trying to create a new Table to just have the minimum cost carrier based on 3 criteria - Origin Zip, Destination Zip, Mode  This is my initial data:   I'm looking for end result to be si...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Anonymous

     

    Another way..Shorter Code

     

    Calculated Table 1 =
GENERATE (
    SUMMARIZE (
        Table1,
        [3 digit Origin Zip],
        [3 digit Destination Zip],
        [Mode],
        "Lowest Rate", MIN ( Table1[Rate] )
    ),
    SELECTCOLUMNS ( FILTER ( Table1, [Rate] = [Lowest Rate] ), "Carrie", [Carrier] )
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
7 years ago

Anonymous

 

One way is to have calculated table from modelling tab

 

Calculated Table =
ADDCOLUMNS (
    SUMMARIZE (
        Table1,
        [3 digit Origin Zip],
        [3 digit Destination Zip],
        [Mode],
        "Lowest Rate", MIN ( Table1[Rate] )
    ),
    "Carrier", CALCULATE (
        MIN ( Table1[Carrier] ),
        TREATAS (
            { ( [3 digit Origin Zip], [3 digit Destination Zip], [Mode], [Lowest Rate] ) },
            Table1[3 digit Origin Zip],
            Table1[3 digit Destination Zip],
            Table1[Mode],
            Table1[Rate]
        )
    )
)