Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
Thank you that is a great solution.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |