Forum Discussion
Solved
DAX virtual summarized table
Hi All,
Please, if any one can help it will be much appreciated. I have the following summarized table to calculate the over all yield, and i cannot see where my mistake is? I get the error at the bottom of the code: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"
here is the code for the measure:
OverAllYield1 =
SUMMARIZE
(
'ABU_Yield-Qty_Data1',
'ABU_Yield-Qty_Data1'[DIE_TYPE],
'ABU_Yield-Qty_Data1'[FT_PRODUCT],
'ABU_Yield-Qty_Data1'[Test Insertion],
'ABU_Yield-Qty_Data1'[Test Flow],
'ABU_Yield-Qty_Data1'[Insertion Temp],
"FT Final Pass Over All Yield Avg1",
CALCULATE
(
AVERAGE
(
'ABU_Yield-Qty_Data1'[Yield (%)]
),
'ABU_Yield-Qty_Data1'[Test Flow]="FT",
'ABU_Yield-Qty_Data1'[Test Insertion]="Final Insertion"
)
)
See if this works for you:
% Yield Val = AVERAGE('Table'[% Yield])Overall Yield = AVERAGEX ( SUMMARIZE ( 'Table', 'Dim Die'[DIE_TYPE], 'Dim Flow'[D flow], 'Dim Ft Product'[FT_PRODUCT], 'Dim Insertion'[D Insertion], 'Dim Lot'[LOTNO], 'Table'[Insertion Temp] ), [% Yield Val] )Yield By Lot = VAR Lot = MAX ( 'Dim Lot'[LOTNO] ) RETURN IF ( ISBLANK ( [% Yield Val] ), BLANK (), AVERAGEX ( FILTER ( ALL ( 'Table' ), RELATED ( 'Dim Lot'[LOTNO] ) = LOT ), [% Yield Val] ) )Yield Change = [Overall Yield] - [Yield By Lot]I've attached the sample BPBIX file
Hi Paul,
I was out for a few days, i tried your code yesterday, and it works.
Thank you very much
Regadrs,
Karim
6 Replies
PaulDBrownCommunity Champion
The problem is that the code returns a table of values (SUMMARIZE returns a table). You can try:
OverAllYield1 = AVERAGEX ( ADDCOLUMNS ( SUMMARIZE ( 'ABU_Yield-Qty_Data1', 'ABU_Yield-Qty_Data1'[DIE_TYPE], 'ABU_Yield-Qty_Data1'[FT_PRODUCT], 'ABU_Yield-Qty_Data1'[Test Insertion], 'ABU_Yield-Qty_Data1'[Test Flow], 'ABU_Yield-Qty_Data1'[Insertion Temp] ), "FT Final Pass Over All Yield Avg1", CALCULATE ( AVERAGE ( 'ABU_Yield-Qty_Data1'[Yield (%)] ), 'ABU_Yield-Qty_Data1'[Test Flow] = "FT", 'ABU_Yield-Qty_Data1'[Test Insertion] = "Final Insertion" ) ), [FT Final Pass Over All Yield Avg1] )Hi Paul,
thanks for the quick response, however, the i am still not able to get the correct yield average by grouping the data based on die type/FT product/Test insertion/test flow and insertion temp. Do you mind providing feedback on the logic of my measure? I am probably doing it wrong.
Thanks for your help
Karim
here is a sample data:
Desired output : yield change = overall yield - yield for each lot;
overall yield= average yield (per die/part/temp/testFlow/Test insertion) over time
sample data
DIE_TYPE FT_PRODUCT LOTNO MEASURE_TIME Yield (%) Insertion Temp Test Flow Test Insertion die1 part1 013A_lot 5/9/2020 59.77 Hot FT Fi_Insertion die1 part1 013A_lot 5/10/2020 97.54 Room FT Fi_Insertion die1 part1 013A_lot 5/14/2020 89.52 Cold QA Mi_Insertion die1 part1 013A_lot 5/10/2020 69.94 Hot QA Mi_Insertion die1 part1 013A_lot 5/11/2020 87.47 Room QA Mi_Insertion die1 part3 013B_lot 5/16/2021 83.14 Room FT CA_Insertion die1 part3 013B_lot 5/15/2021 60.01 Hot FT CA_Insertion die1 part3 013B_lot 5/17/2021 86.2 Cold FT CA_Insertion die1 part3 013B_lot 5/15/2021 82.35 Hot FT Fi_Insertion die1 part3 013B_lot 5/15/2021 70.22 Hot FT Fi_Insertion die1 part1 01BB_lot 4/12/2019 95.91 Room FT CA_Insertion die1 part1 01BB_lot 4/10/2019 67.55 Hot FT CA_Insertion die1 part1 01BB_lot 4/22/2019 87.31 Cold FT CA_Insertion die2 part4 004A_lot 5/24/2019 95.54 Hot FT Fi_Insertion die2 part4 004A_lot 5/24/2019 89.93 Hot FT Fi_Insertion die2 part4 004A_lot 5/26/2019 99.59 Room FT Fi_Insertion die2 part4 004A_lot 5/26/2019 89.71 Room FT Fi_Insertion die2 part4 004A_lot 5/28/2019 97.8 Cold FT Fi_Insertion die2 part4 004A_lot 5/28/2019 94.53 Cold FT Fi_Insertion die2 part4 04AA_lot 6/21/2019 89.88 Room FT CA_Insertion die2 part4 04AA_lot 6/17/2019 85.15 Hot FT CA_Insertion die2 part4 04AA_lot 6/23/2019 92.36 Cold FT CA_Insertion die2 part4 04AA_lot 6/17/2019 94.06 Hot FT Fi_Insertion die2 part4 04AA_lot 6/17/2019 89.17 Hot FT Fi_Insertion die2 part4 04AA_lot 6/21/2019 90.88 Room FT Fi_Insertion die2 part4 04AA_lot 6/21/2019 90.88 Room FT Fi_Insertion die2 part4 04AA_lot 6/23/2019 98.46 Cold FT Fi_Insertion die2 part4 04AA_lot 6/23/2019 92.52 Cold FT Fi_Insertion die2 part4 04AA_lot 6/25/2019 93.37 Cold QA Mi_Insertion die2 part4 04AC_lot 7/3/2019 83.47 Cold FT CA_Insertion die2 part4 04AC_lot 7/2/2019 98.82 Room FT Fi_Insertion die2 part4 04AC_lot 7/2/2019 90.4 Room FT Fi_Insertion die2 part4 04AC_lot 7/3/2019 97.74 Cold FT Fi_Insertion die2 part4 04AC_lot 7/3/2019 87.43 Cold FT Fi_Insertion die2 part4 04AC_lot 7/4/2019 90.24 Cold QA Mi_Insertion die2 part2 004B_lot 5/29/2019 93.38 Hot FT Fi_Insertion die2 part2 004B_lot 5/31/2019 95.53 Room FT Fi_Insertion die2 part2 004B_lot 5/31/2019 92.19 Cold FT Fi_Insertion die2 part2 004B_lot 5/29/2019 97.33 Hot FT Fi_Insertion die2 part2 004B_lot 5/31/2019 99.51 Room FT Fi_Insertion die2 part2 004B_lot 5/31/2019 98.25 Cold FT Fi_Insertion die2 part2 04BA_lot 6/7/2019 96.08 Room FT CA_Insertion die2 part2 04BA_lot 6/9/2019 91.9 Cold FT CA_Insertion die2 part2 04BA_lot 6/3/2019 92.39 Hot FT Fi_Insertion die2 part2 04BA_lot 6/7/2019 96.17 Room FT Fi_Insertion die5 part2 029B_lot 2/14/2022 94.4 Hot FT CA_Insertion die5 part2 029C_lot 2/26/2022 97.47 Hot FT Fi_Insertion die5 part2 029C_lot 3/1/2022 98.54 Cold FT Fi_Insertion die5 part2 029C_lot 2/26/2022 98.35 Hot FT Fi_Insertion die5 part2 30CA_lot 1/29/2022 95.85 Hot FT CA_Insertion die5 part4 031A_lot 2/10/2022 92.14 Hot FT Fi_Insertion die5 part4 031A_lot 2/15/2022 93.05 Cold FT Fi_Insertion die5 part4 031A_lot 2/10/2022 96.47 Hot FT Fi_Insertion die5 part4 031A_lot 2/15/2022 95.67 Cold FT Fi_Insertion die5 part4 031A_lot 2/15/2022 92.46 Cold FT CA_Insertion die5 part4 031A_lot 2/10/2022 90.17 Hot FT CA_Insertion die5 part4 031B_lot 2/7/2022 93.83 Hot FT Fi_Insertion die5 part4 031B_lot 2/9/2022 91.63 Cold FT Fi_Insertion die5 part4 031B_lot 2/7/2022 96.12 Hot FT Fi_Insertion die5 part2 035C_lot 2/26/2022 94.64 Cold FT CA_Insertion die5 part2 35CA_lot 3/1/2022 94.1 Cold FT Fi_Insertion die5 part2 35CA_lot 3/1/2022 95.87 Cold FT Fi_Insertion die5 part2 35CA_lot 2/28/2022 93.72 Cold FT CA_Insertion