Power BI Measure实现静态取最大年份Cost值的问题排查

问题背景 #

用于Power BI的测试数据通过以下SQL生成：

IF OBJECT_ID('tempdb..#TBL') IS NOT NULL DROP TABLE #TBL
create table #TBL (category varchar(6), Year_Vs_Year VARCHAR(20),   Cost INT)
INSERT INTO #TBL VALUES
(null,'24/25 vs 19/20',7),(null,'24/25 vs 20/21',208),(null,'24/25 vs 21/22',98),
(null,'24/25 vs 22/23',28),(null,'24/25 vs 23/24',67),(null,'24/25 vs 24/25',28),
('AE','24/25 vs 19/20',881959),('AE','24/25 vs 20/21',1424903),('AE','24/25 vs 21/22',3052440),
('AE','24/25 vs 22/23',1975536),('AE','24/25 vs 23/24',1524131),('AE','24/25 vs 24/25',956217),
('DP','24/25 vs 19/20',133),('DP','24/25 vs 20/21',6),('DP','24/25 vs 21/22',20),
('DP','24/25 vs 22/23',6),('DP','24/25 vs 23/24',17),('DY','24/25 vs 19/20',66587),
('DY','24/25 vs 20/21',53731),('DY','24/25 vs 21/22',55157),('DY','24/25 vs 22/23',68488),
('DY','24/25 vs 23/24',75492),('DY','24/25 vs 24/25',46234),('EV','24/25 vs 19/20',93),
('EV','24/25 vs 20/21',50),('EV','24/25 vs 21/22',342),('EV','24/25 vs 23/24',714),('EV','24/25 vs 24/25',64),
('GP','24/25 vs 19/20',3513842),('GP','24/25 vs 20/21',2499217),('GP','24/25 vs 21/22',3266166),
('GP','24/25 vs 22/23',3517609),('GP','24/25 vs 23/24',4133396),('GP','24/25 vs 24/25',2804384),
('IP','24/25 vs 19/20',2258417),('IP','24/25 vs 20/21',2622805),
('IP','24/25 vs 21/22',3613080),('IP','24/25 vs 22/23',2905209),('IP','24/25 vs 23/24',2632569),('IP','24/25 vs 24/25',1768455),
('OH','24/25 vs 19/20',42040),('OH','24/25 vs 20/21',73167),('OH','24/25 vs 21/22',81570),('OH','24/25 vs 22/23',27576),
('OH','24/25 vs 23/24',23651),('OH','24/25 vs 24/25',14776),('OP','24/25 vs 19/20',2185227),('OP','24/25 vs 20/21',1973621),
('OP','24/25 vs 21/22',3078435),('OP','24/25 vs 22/23',2795034),('OP','24/25 vs 23/24',2735693),('OP','24/25 vs 24/25',1791504),
('PP','24/25 vs 19/20',74566),('PP','24/25 vs 20/21',27627),('PP','24/25 vs 21/22',22976),('PP','24/25 vs 22/23',12624),
('PP','24/25 vs 23/24',11224),('PP','24/25 vs 24/25',2928),('QC','24/25 vs 19/20',42588),('QC','24/25 vs 20/21',44024),
('QC','24/25 vs 21/22',64487),('QC','24/25 vs 22/23',69394),('QC','24/25 vs 23/24',84978),('QC','24/25 vs 24/25',48377),
('RE','24/25 vs 19/20',32),('RE','24/25 vs 20/21',44),('UN','24/25 vs 19/20',46788),('UN','24/25 vs 20/21',302300),
('UN','24/25 vs 21/22',368105),('UN','24/25 vs 22/23',116676),('UN','24/25 vs 23/24',58059),('UN','24/25 vs 24/25',42589)
select * from #TBL

原编写的DAX Measure返回全表Cost总和，不符合需求，期望所有行显示最大年份对应的Cost值：

Cost without current year = 
var a = MAX(Pathology[fiscal_year])
var b =CALCULATE(([Cost]),a= Pathology[fiscal_year])
var c = CALCULATE(SUM(Pathology[Cost]),ALL('Pathology'))
RETURN
c

原DAX问题分析 #

• 变量c使用ALL('Pathology')清除了所有筛选上下文，直接返回全表Cost总和，完全未实现需求
• 变量b的筛选逻辑未被使用，属于冗余代码

修正后的DAX Measure #

场景1：全局最大年份的Cost总和 #

所有行统一显示整个表中最大年份对应的Cost总和，忽略其他维度筛选：

Cost without current year = 
VAR MaxFiscalYear = MAX(Pathology[fiscal_year])
RETURN
CALCULATE(
    SUM(Pathology[Cost]),
    Pathology[fiscal_year] = MaxFiscalYear,
    ALL(Pathology[fiscal_year]) // 清除当前行的年份筛选，确保取全局最大年份数据
)

场景2：按Category分组的最大年份Cost #

每个Category下的行显示该分类内最大年份对应的Cost值，保留Category上下文：

Cost without current year = 
VAR CurrentCategory = SELECTEDVALUE(Pathology[category])
VAR MaxFiscalYear = CALCULATE(MAX(Pathology[fiscal_year]), Pathology[category] = CurrentCategory)
RETURN
CALCULATE(
    SUM(Pathology[Cost]),
    Pathology[category] = CurrentCategory,
    Pathology[fiscal_year] = MaxFiscalYear
)

说明 #

• 场景1适用于需要统一展示全局最新年份Cost总和的场景
• 场景2适用于测试数据中按Category分组的业务需求，确保每个分类显示自身最新年份的Cost值

内容的提问来源于stack exchange，提问作者JonWay