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
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] ) )
)
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        AlB (I'm not a DAX expert) When I used the one you posted it gives me all the values rather than Just least cost carrier.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Works like a Charm! Thanks Zubair_Muhammad