Power Bi中具有多个方案的查找值 [英] Lookup value with multiple scenario in Power Bi

问题描述

我有两个表，分别是数据表和报告表.

I have a two tables are Data and Report.

数据表:

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

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是否记录在数据中的摘要:

如果任何一项在数据表中都有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记录的摘要

如果数据表中仅一个无CHE记录则返回实际值，但是如果此时数据表中有多个无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.

方案1；

如果两个不同的供应商{"CHE"&对于数据表中的同一项目，我想根据该项目针对报告表中的"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.

当前逻辑

Current logic

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

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.

我的最终结果看起来像这样

My final result look like this

此处随附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屋！