cancel
Showing results for
Did you mean:
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:

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

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
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

## 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 =
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

## 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

## 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] ) )
)```
Regular Visitor

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

Works like a Charm! Thanks @Zubair_Muhammad

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

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