Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KarimMouloua
Regular Visitor

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"

)
)

 

KarimMouloua_0-1647277844728.png

 

2 ACCEPTED SOLUTIONS

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]

result.jpg

 

I've attached the sample BPBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Hi Paul,

 

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

Thank you very much

 

Regadrs,

Karim

View solution in original post

6 REPLIES 6
PaulDBrown
Community Champion
Community 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]
)




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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_TYPEFT_PRODUCTLOTNOMEASURE_TIMEYield (%)Insertion TempTest FlowTest Insertion
die1part1013A_lot5/9/202059.77HotFTFi_Insertion
die1part1013A_lot5/10/202097.54RoomFTFi_Insertion
die1part1013A_lot5/14/202089.52ColdQAMi_Insertion
die1part1013A_lot5/10/202069.94HotQAMi_Insertion
die1part1013A_lot5/11/202087.47RoomQAMi_Insertion
die1part3013B_lot5/16/202183.14RoomFTCA_Insertion
die1part3013B_lot5/15/202160.01HotFTCA_Insertion
die1part3013B_lot5/17/202186.2ColdFTCA_Insertion
die1part3013B_lot5/15/202182.35HotFTFi_Insertion
die1part3013B_lot5/15/202170.22HotFTFi_Insertion
die1part101BB_lot4/12/201995.91RoomFTCA_Insertion
die1part101BB_lot4/10/201967.55HotFTCA_Insertion
die1part101BB_lot4/22/201987.31ColdFTCA_Insertion
die2part4004A_lot5/24/201995.54HotFTFi_Insertion
die2part4004A_lot5/24/201989.93HotFTFi_Insertion
die2part4004A_lot5/26/201999.59RoomFTFi_Insertion
die2part4004A_lot5/26/201989.71RoomFTFi_Insertion
die2part4004A_lot5/28/201997.8ColdFTFi_Insertion
die2part4004A_lot5/28/201994.53ColdFTFi_Insertion
die2part404AA_lot6/21/201989.88RoomFTCA_Insertion
die2part404AA_lot6/17/201985.15HotFTCA_Insertion
die2part404AA_lot6/23/201992.36ColdFTCA_Insertion
die2part404AA_lot6/17/201994.06HotFTFi_Insertion
die2part404AA_lot6/17/201989.17HotFTFi_Insertion
die2part404AA_lot6/21/201990.88RoomFTFi_Insertion
die2part404AA_lot6/21/201990.88RoomFTFi_Insertion
die2part404AA_lot6/23/201998.46ColdFTFi_Insertion
die2part404AA_lot6/23/201992.52ColdFTFi_Insertion
die2part404AA_lot6/25/201993.37ColdQAMi_Insertion
die2part404AC_lot7/3/201983.47ColdFTCA_Insertion
die2part404AC_lot7/2/201998.82RoomFTFi_Insertion
die2part404AC_lot7/2/201990.4RoomFTFi_Insertion
die2part404AC_lot7/3/201997.74ColdFTFi_Insertion
die2part404AC_lot7/3/201987.43ColdFTFi_Insertion
die2part404AC_lot7/4/201990.24ColdQAMi_Insertion
die2part2004B_lot5/29/201993.38HotFTFi_Insertion
die2part2004B_lot5/31/201995.53RoomFTFi_Insertion
die2part2004B_lot5/31/201992.19ColdFTFi_Insertion
die2part2004B_lot5/29/201997.33HotFTFi_Insertion
die2part2004B_lot5/31/201999.51RoomFTFi_Insertion
die2part2004B_lot5/31/201998.25ColdFTFi_Insertion
die2part204BA_lot6/7/201996.08RoomFTCA_Insertion
die2part204BA_lot6/9/201991.9ColdFTCA_Insertion
die2part204BA_lot6/3/201992.39HotFTFi_Insertion
die2part204BA_lot6/7/201996.17RoomFTFi_Insertion
die5part2029B_lot2/14/202294.4HotFTCA_Insertion
die5part2029C_lot2/26/202297.47HotFTFi_Insertion
die5part2029C_lot3/1/202298.54ColdFTFi_Insertion
die5part2029C_lot2/26/202298.35HotFTFi_Insertion
die5part230CA_lot1/29/202295.85HotFTCA_Insertion
die5part4031A_lot2/10/202292.14HotFTFi_Insertion
die5part4031A_lot2/15/202293.05ColdFTFi_Insertion
die5part4031A_lot2/10/202296.47HotFTFi_Insertion
die5part4031A_lot2/15/202295.67ColdFTFi_Insertion
die5part4031A_lot2/15/202292.46ColdFTCA_Insertion
die5part4031A_lot2/10/202290.17HotFTCA_Insertion
die5part4031B_lot2/7/202293.83HotFTFi_Insertion
die5part4031B_lot2/9/202291.63ColdFTFi_Insertion
die5part4031B_lot2/7/202296.12HotFTFi_Insertion
die5part2035C_lot2/26/202294.64ColdFTCA_Insertion
die5part235CA_lot3/1/202294.1ColdFTFi_Insertion
die5part235CA_lot3/1/202295.87ColdFTFi_Insertion
die5part235CA_lot2/28/202293.72ColdFTCA_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]

result.jpg

 

I've attached the sample BPBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi Paul,

 

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

Thank you very much

 

Regadrs,

Karim

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.