cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
koganti333 Regular Visitor
Regular Visitor

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:

1.PNG

 

I'm looking for end result to be similar to this:

2.PNG

Thanks 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

@koganti333

 

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 6
Super User
Super User

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

@koganti333

 

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

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

@koganti333

 

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] )
)
Highlighted
Super User
Super User

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

@koganti333

 

...and another one:

 

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

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

Works like a Charm! Thanks @Zubair_Muhammad

koganti333 Regular Visitor
Regular Visitor

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

@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.

Super User
Super User

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

@koganti333

 

Maybe you did something differently. It should work.

Check it out in this file. It yields exactly the same result as you showed above.