DAX virtual summarized table

[KarimMouloua]

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"

)

)

[PaulDBrown · Accepted solution]

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

[PaulDBrown]

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]
)

[KarimMouloua]

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

[KarimMouloua]

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

[PaulDBrown · Accepted solution]

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

[KarimMouloua · Accepted solution]

Hi Paul,

I was out for a few days, i tried your code yesterday, and it works.

Thank you very much

Regadrs,

Karim

[PaulDBrown]

It would be more productive if you provided sample data and a depiction of the expected outcome. Otherwise we are basically guessing!