Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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:

STATEMARKETVENDORLENGTHCOST
ALBHCOMPANY_A10002500
ALBHCOMPANY_B10002250
ALBHCOMPANY_C10002150
ALBHCOMPANY_D10002000
ALBHCOMPANY_E10002200
ALMOCOMPANY_A10001000
ALMOCOMPANY_B10001100
ALMOCOMPANY_C10001400
ALMOCOMPANY_D10001300
MABNCOMPANY_A1000200
MABNCOMPANY_B1000300
MABNCOMPANY_C1000400
MABNCOMPANY_D1000500
ALBHCOMPANY_A100003000
ALBHCOMPANY_B100003100
ALBHCOMPANY_C100003200
MABNCOMPANY_A500700
MABNCOMPANY_B500600
MABNCOMPANY_C500800
MABNCOMPANY_D500900

 

Expected Results:

STATEMARKETVENDORLENGTHCOSTMIN_COST
ALBHCOMPANY_A100025002000
ALBHCOMPANY_B100022502000
ALBHCOMPANY_C100021502000
ALBHCOMPANY_D100020002000
ALBHCOMPANY_E100022002000
ALMOCOMPANY_A100010001000
ALMOCOMPANY_B100011001000
ALMOCOMPANY_C100014001000
ALMOCOMPANY_D100013001000
MABNCOMPANY_A1000200200
MABNCOMPANY_B1000300200
MABNCOMPANY_C1000400200
MABNCOMPANY_D1000500200
ALBHCOMPANY_A1000030003000
ALBHCOMPANY_B1000031003000
ALBHCOMPANY_C1000032003000
MABNCOMPANY_A500700600
MABNCOMPANY_B500600600
MABNCOMPANY_C500800600
MABNCOMPANY_D500900600
1 ACCEPTED SOLUTION
PANDAmonium
Resolver III
Resolver III

Create a new measure that's something like:
Minimum Cost = CALCULATE(MIN('Table'[COST]), REMOVEFILTERS('Table'[VENDOR], 'Table'[COST]))
 
Capture.PNG

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Thank you that is a great solution.

PANDAmonium
Resolver III
Resolver III

Create a new measure that's something like:
Minimum Cost = CALCULATE(MIN('Table'[COST]), REMOVEFILTERS('Table'[VENDOR], 'Table'[COST]))
 
Capture.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.