How to find the minimum cost in a table by state, market and length in Power BI

[Anonymous]

I have a table that contains various values for State, Market, Length and Cost. I need to calculate a new measure showing the minimum cost available by state/market/length. I tried using Min with FILTER but the results are incorrect.

DAX:

MIN_COST=
var current_row_mkt = TABLE1[MARKET]
var current_row_state = TABLE1[STATE]
var current_row_lth = TABLE1[LENGTH]
RETURN
CALCULATE(
MIN(TABLE1[COST]),
FILTER(
ALL(TABLE1),
TABLE1[STATE_cd] = current_row_state),
FILTER(
ALL(TABLE1),
TABLE1[MARKET]=current_row_mkt),
FILTER(
ALL(TABLE1),
TABLE1[LENGTH]=current_row_lth)
)

Table1:

STATE	MARKET	VENDOR	LENGTH	COST
AL	BH	COMPANY_A	1000	2500
AL	BH	COMPANY_B	1000	2250
AL	BH	COMPANY_C	1000	2150
AL	BH	COMPANY_D	1000	2000
AL	BH	COMPANY_E	1000	2200
AL	MO	COMPANY_A	1000	1000
AL	MO	COMPANY_B	1000	1100
AL	MO	COMPANY_C	1000	1400
AL	MO	COMPANY_D	1000	1300
MA	BN	COMPANY_A	1000	200
MA	BN	COMPANY_B	1000	300
MA	BN	COMPANY_C	1000	400
MA	BN	COMPANY_D	1000	500
AL	BH	COMPANY_A	10000	3000
AL	BH	COMPANY_B	10000	3100
AL	BH	COMPANY_C	10000	3200
MA	BN	COMPANY_A	500	700
MA	BN	COMPANY_B	500	600
MA	BN	COMPANY_C	500	800
MA	BN	COMPANY_D	500	900

Expected Results:

STATE	MARKET	VENDOR	LENGTH	COST	MIN_COST
AL	BH	COMPANY_A	1000	2500	2000
AL	BH	COMPANY_B	1000	2250	2000
AL	BH	COMPANY_C	1000	2150	2000
AL	BH	COMPANY_D	1000	2000	2000
AL	BH	COMPANY_E	1000	2200	2000
AL	MO	COMPANY_A	1000	1000	1000
AL	MO	COMPANY_B	1000	1100	1000
AL	MO	COMPANY_C	1000	1400	1000
AL	MO	COMPANY_D	1000	1300	1000
MA	BN	COMPANY_A	1000	200	200
MA	BN	COMPANY_B	1000	300	200
MA	BN	COMPANY_C	1000	400	200
MA	BN	COMPANY_D	1000	500	200
AL	BH	COMPANY_A	10000	3000	3000
AL	BH	COMPANY_B	10000	3100	3000
AL	BH	COMPANY_C	10000	3200	3000
MA	BN	COMPANY_A	500	700	600
MA	BN	COMPANY_B	500	600	600
MA	BN	COMPANY_C	500	800	600
MA	BN	COMPANY_D	500	900	600

[Anonymous]

Thank you that is a great solution.

[PANDAmonium · Accepted solution]

Create a new measure that's something like:

Minimum Cost = CALCULATE(MIN('Table'[COST]), REMOVEFILTERS('Table'[VENDOR], 'Table'[COST]))