英文:
Create new column, store value from another table in Power BI
问题
这似乎是一个非常简单的任务，但似乎什么都不起作用。
在FCT总账中，有一列名为[EC_Value]，每一行都包含值0或1。
如果存在匹配的Contract_FK，并且FCT总账中的值为1，则我也想在DIM Contract中得到该值。
以下是相关的表格及其相关列：
FCT总账
- contract_fk
- EC_Value*
DIM合同
- Contract_PK
我尝试了以下公式来在DIM合同中创建该列。
EC Included =
VAR curValue = SELECTEDVALUE('DIM合同'[Contract_PK])
VAR ecValue =
CALCULATE(
SUM('FCT总账'[EC_Value]),
FILTER('FCT总账', 'FCT总账'[contract_fk] = curValue )
)
RETURN
IF(
ecValue = 1,
1,
0
)
这给了我DIM合同中每一行都是0的结果。
EC Included = LOOKUPVALUE('FCT总账'[EC_Value], 'FCT总账'[contract_fk], 'DIM合同'[Contract_pk])
这会得到以下错误消息：提供了一个包含多个值的表格，但预期是单个值。
EC Included =
VAR curValue = SELECTEDVALUE('DIM合同'[Contract_PK])
RETURN
CALCULATE(MAX('FCT总账'[EC_Value]), 'FCT总账'[contract_fk]= curValue)
这返回所有的空白，并且只在一行中填入了1。因此，这似乎是最接近解决方案的方法，但它只在Contract_PK = 0的行中填入了1。
我认为这意味着我没有正确使用SELECTEDVALUE方法，因为我可能没有对每个值进行迭代。
英文:
This seems like a super easy task, but nothing seems to work.
In FCT General Ledger, there is a column named [EC_Value], every row contains the value 0 or 1.
If there is a matching Contract_FK, and the value in FCT General Ledger is 1, then I would like to have that value in DIM Contract as well.
These are the relevant tables with relevant columns:
FCT General Ledger
- *contract_fk
- EC_Value*
DIM Contract
- Contract_PK
I tried the following formulas to create the column in DIM Contract.
EC Included =
VAR curValue = SELECTEDVALUE('DIM Contract'[Contract_PK])
VAR ecValue =
CALCULATE(
SUM('FCT General Ledger'[EC_Value]),
FILTER('FCT General Ledger', 'FCT General Ledger'[contract_fk] = curValue )
)
RETURN
IF(
ecValue = 1,
1,
0
)
This gives me a 0 in every row in DIM Contract.
EC Included = LOOKUPVALUE('FCT General Ledger'[EC_Value], 'FCT General Ledger'[contract_fk], 'DIM Contract'[Contract_pk])
This gives the following error message:
A table of multiple values was supplied where a single value was expected.
EC Included =
VAR curValue = SELECTEDVALUE('DIM Contract'[Contract_PK])
RETURN
Calculate(MAX('FCT General Ledger'[EC_Value]),'FCT General Ledger'[contract_fk]= curValue)
This returns all blanks, and a 1 in only one row. So this seems to be the closest to the solution, but it only filled in the 1 at the row where the Contract_PK = 0.
I think this means I am not correctly using the SELECTEDVALUE method, because I might not iterating over every value.
答案1
得分: 1
与此同时，我想出了一个可行的解决方案。也许还有更清晰的解决方案。所以请随时纠正我。
EC 值 =
VAR curValue = VALUE ('DIM Contract'[Contract_PK])
RETURN
Calculate(MAX('FCT General Ledger'[EC Value]),'FCT General Ledger'[contract_fk]= curValue)
英文:
Meanwhile, I came up with something that works.
There might be a cleaner solution out there, though. So feel free to correct me.
EC value =
VAR curValue = VALUE ('DIM Contract'[Contract_PK])
RETURN
Calculate(MAX('FCT General Ledger'[EC Value]),'FCT General Ledger'[contract_fk]= curValue)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论