Power Bi 中多场景的查找值 [英] Lookup value with multiple scenario in Power Bi

问题描述

我有两个表，分别是 Data 和 Report.

I have a two tables are Data and Report.

数据表:

在数据表中包含两列是项目和供应商代码.item 列包含文本 &编号和供应商代码列包含为文本.

In Data table contain two columns are item and supplier code. The item column contain text & number and supplier code column contain as a text.

如果 CHE 记录在 DATA 中，则总结:

如果数据表中有任何一项有 CHE 记录，如果只有一个 CHE 记录，则获取值，如果有多个 CHE 记录，则比较每一列(长度和供应商)，如果任何一列有不同的值然后返回 MIXED 否则返回实际值

if any of the items have CHE records in the data table, get the values if only one CHE record, and if there is more than one CHE record then compare each column (length and Supplier) and if any of the columns has different value then return MIXED otherwise return Actual value

没有 CHE 记录的总结

如果数据表中只有一条none CHE记录则返回实际值，但如果该时间数据表中有多个none CHE记录，则比较每一列的值，如果任何列值不同则返回混合其他价值.

if only one none CHE record in the data table then returns the actual values but if more than one none CHE records in the data table for that time, compare the values of each column, and if any column value is different then return MIXED otherwise value.

总结:在 DATA 表中找不到记录:

Summary: no record found in DATA table:

返回不适用逻辑

我的第一优先级是CHE"，第二优先级是KKR"，以便将数据表中的长度和供应商获取到报告表中.

My first priority is "CHE" and second priority is "KKR" in order get the length and supplier from Data table into the Report Table.

场景一；

如果两个不同的供应商 {CHE" &KKR"}对于数据表中的同一项目，然后我想根据项目在报告表中获取针对CHE"的数据(长度和供应商).(请参阅数据"和报告"表中的第 123 和 5678 项)

If two different supplier {"CHE" & "KKR"} for the same item in data table then I would like to get the data (length and supplier) against "CHE" in report table according to the item. (Please refer the item 123 and 5678 in both tables "Data" and "Report")

场景2；

如果供应商没有根据项目重复，并且项目在数据表中包含这些供应商{CHE"或KKR"}中的任何一个，则在这些场景中，将数据(长度和供应商)与报告表中的项目相对应基于数据表中的可用性.(请参阅数据"和报告"表中的项目5555、6666、9876和2345)

If supplier not repeated according to the item and the item contain either any of these supplier {"CHE" or "KKR"} in data table, in these scenario pull the data (length and supplier) against the item in report table based on the availability in data table. (Please refer the item 5555, 6666, 9876 and 2345 in both tables "Data" and "Report")

场景 3；

如果供应商没有根据项目重复，并且项目包含这些供应商{CHE"或KKR"}中的任何一个，但长度和在这些方案中，供应商在数据表中有所不同，因为根据项目返回的文本混合"在报告表中属于(列；长度或供应商或两者).(请参阅数据"和报告"两个表中的项目123456和567)

If supplier not repeated according to the item and the item contain either any of these supplier {"CHE" or "KKR"} but the length & supplier are different in data table in these scenario return as a text "Mixed" were belongs to (column; either length or supplier or both) in report table according to the item. (Please refer the item 123456 and 567 in both tables "Data" and "Report")

情景4；

如果两个不同的供应商 {CHE" &KKR"} 用于数据表中的同一项目，但在此方案中，长度和供应商在数据表中是可变的，根据物品.(请参阅数据"和报告"两个表中的第 116 项)

If two different supplier {"CHE" & "KKR"} for the same item in data table but the length and supplier are variable in data table in this scenario return as a text "Mixed" were belongs to (column; either length or supplier or both) in report table according to the item. (Please refer the item 116 in both tables "Data" and "Report")

场景 5；

如果在日期表中没有找到记录，则根据项目在报告表中返回NA".(请参考表4444、12、10报告项目)

If records not found in date table the return as "NA" in report table according to the item. (Please refer the items in report in table 4444, 12 and 10)

我正在寻找计算列选项以实现我的结果.

I am looking for calculated column option in order to achieve my result.

当前逻辑

我为供应商优先级的订单创建了新表

I created New table for order for supplier priority

我为运行计数和唯一计数创建了两个帮助列

I created two helper column for running count and unique count

最后我在报告表中应用以下计算列

Finally I am applying the following calculated column in report table

LENGTH = LOOKUPVALUE(
    DATA[Length],
    DATA[ITEM],REPORT[ITEM],
    DATA[UNIQUE_COUNT],1,"NA")

它几乎可以正常工作，但如果我想要的是混合"，它会给出错误的结果(项目 123456 和 567).在报告表中.我以红色和绿色的快照突出显示供您参考.

it almost working fine but it will give a wrong result (item 123456 and 567) were my desired is "Mixed" in report table. I highlighted in red and green snapshot for your reference.

我的最终结果是这样的

特此附上 PowerBi 文件供您参考 https://www.dropbox.com/s/p81uy12tfh1htwu/AUOM1.pbix?dl=0

Herewith attached the PowerBi file for your reference https://www.dropbox.com/s/p81uy12tfh1htwu/AUOM1.pbix?dl=0

请给点建议.

推荐答案

尚不完全清楚您要做什么，但我认为这种度量模式可能会对您有所帮助:

It's not entirely clear what you're trying to do but I think this measure pattern may help you:

IF (
    DISTINCTCOUNT ( DATA[SUPPLIER] ) > 1,
    "MIXED",
    SELECTEDVALUE ( DATA[SUPPLIER], "NA" )
)

对于多个值，您会得到 MIXED.对于一个值，您将获得该单个值.如果没有值，您会得到 NA.

For more than one value, you get MIXED. For one value, you get that single value. For no values, you get NA.

这篇关于Power Bi 中多场景的查找值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！