Forum Discussion

KarimMouloua's avatar
KarimMouloua
Regular Visitor
4 years ago
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"

)
)

 

 

measure issue
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    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's avatar
    KarimMouloua
    4 years ago

    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

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    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's avatar
      KarimMouloua
      Regular Visitor
      4 years ago

      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

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        4 years ago

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