powerbi - 如何在 Power BI 中按州和市场查找表中的最低成本

Question

我有一个表，其中包含状态、市场、长度和成本的各种值。我需要计算一个新的衡量标准，显示州/市场可用的最低成本。我尝试将 Min 与 FILTER 一起使用，但结果不正确。

COST min =
CALCULATE (
    MIN ( Table1[COST] ),
    FILTER ( Table1, MIN ( Table1[LENGTH] ) ),
    Table1[LENGTH]    )

表格1：

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 

预期成绩：

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

标签： powerbidax

Answer 1

这是下面给出的测量和计算列的代码 -

措施

min_value_measure = 

var current_row_market = MIN(your_table_name[MARKET])
var current_row_length = MIN(your_table_name[LENGTH])

RETURN
CALCULATE(
    MIN(your_table_name[COST]),
    FILTER(
        ALL(your_table_name),
        your_table_name[MARKET] = current_row_market
        && your_table_name[LENGTH] = current_row_length 
    )
)

计算列

min_value_column = 

var current_row_market = your_table_name[MARKET]
var current_row_length = your_table_name[LENGTH]

RETURN
CALCULATE(
    MIN(your_table_name[COST]),
    FILTER(
        ALL(your_table_name),
        your_table_name[MARKET] = current_row_market
        && your_table_name[LENGTH] = current_row_length
    )
)

最终输出-