基于多种条件的Power BI返回价值

发布于 2025-01-29 04:10:01 字数 1279 浏览 2 评论 0原文

我有下面的数据

create table #data (Post_Code varchar(10),  Internal_Code varchar(10))
insert into #data values
('AB10','hb3'),('AB10','hb25'),('AB12','dd1'),('AB15','hb6'),('AB16','aa4'),('AB16','hb7'),
('AB16','aa2'),('AB16','ab9'),('AB18','rr6'),('AB18','rr9'),('AB18','hb10'),('AB20','rr15'),
('AB20','td2'),('AB21','hb8'),('AB21','cc4'),('AB21','cc4'),('AB24','td5'),('AB9','yy3'),
('RM2','CC1'),('RM6','hb6'),('RM7','cc2'),('SA24','rr1'),('SA24','hb5'),('SA24','rr2'),
('SA24','cc34'),('SE15','rr9'),('SE15','rr5'),('SE25','rr10'),('SE25','hb11'),('SE25','rr8'),
('SE25','rr1'),('LA15','rr2')

select * from #data
drop table #data

要实现的目标是，如果同一邮政编码区域具有“ HB”或“ RR”，我想返回1 else 1 else 0 0 如果“ HB”或“ RR”内部_Code在不同的邮政编码中，则必须在同一post_code中。我应该

写这本dax，

Result = IF(left(Data[Internal_Code],2)="hb" || left(Data[Internal_Code],2)="rr",1,0)

它没有返回正确的结果

电流输出

预期输出

原文

I have the data below

create table #data (Post_Code varchar(10),  Internal_Code varchar(10))
insert into #data values
('AB10','hb3'),('AB10','hb25'),('AB12','dd1'),('AB15','hb6'),('AB16','aa4'),('AB16','hb7'),
('AB16','aa2'),('AB16','ab9'),('AB18','rr6'),('AB18','rr9'),('AB18','hb10'),('AB20','rr15'),
('AB20','td2'),('AB21','hb8'),('AB21','cc4'),('AB21','cc4'),('AB24','td5'),('AB9','yy3'),
('RM2','CC1'),('RM6','hb6'),('RM7','cc2'),('SA24','rr1'),('SA24','hb5'),('SA24','rr2'),
('SA24','cc34'),('SE15','rr9'),('SE15','rr5'),('SE25','rr10'),('SE25','hb11'),('SE25','rr8'),
('SE25','rr1'),('LA15','rr2')

select * from #data
drop table #data

What I want to achieve is if the same post code area have “hb” or “rr” in the same post code I want to return 1 else 0
The “hb” or “rr” internal_code must be in the same post_code if they in different post code. It should be 0

I wrote this DAX

Result = IF(left(Data[Internal_Code],2)="hb" || left(Data[Internal_Code],2)="rr",1,0)

it is not returning the correct result

current output

expected output

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

吾性傲以野 2025-02-05 04:10:01

我认为您的预期结果不正确，因为SA24也应该是1。您绝对应该在PQ中进行类似的计算，但是如果您需要在计算的列中DAX中进行此操作，则使用以下代码。

Result = 

VAR post_code = Data[Post_code]

RETURN

VAR hb = CALCULATE (COUNTROWS(Data),'Data'[Post_code] = post_code && left(Data[Internal_Code],2) = "hb" )
VAR rr = CALCULATE (COUNTROWS(Data),'Data'[Post_code] = post_code && left(Data[Internal_Code],2) = "rr" ) 

RETURN IF(hb>0 && rr > 0,1)

I think your expected result is incorrect as SA24 should also be 1. You should definitely do a calculation like this in PQ but if you need to do it in DAX in a calculated column, then use the following code which works.

Result = 

VAR post_code = Data[Post_code]

RETURN

VAR hb = CALCULATE (COUNTROWS(Data),'Data'[Post_code] = post_code && left(Data[Internal_Code],2) = "hb" )
VAR rr = CALCULATE (COUNTROWS(Data),'Data'[Post_code] = post_code && left(Data[Internal_Code],2) = "rr" ) 

RETURN IF(hb>0 && rr > 0,1)