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 similar to this:

Thanks 

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] )
)

6 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community 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]
        )
    )
)
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      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] )
)
      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        Anonymous

         

        ...and another one:

         

        ResultTable =
FILTER (
    Table1;
    Table1[Rate] = CALCULATE ( MIN ( Table1[Rate] ); ALL ( Table1[Rate]; Table1[Carrier] ) )
)