Ошибка ожидалось единственное значение однако передана таблица или несколько значений

I’m creating a PowerBI dashboard for supply chain backorders. There was a great video I saw from Microsoft demonstrating a Dynamic message card on their Destiny dashboard showing players and their favorite weapon, https://youtu.be/uHP7YJH526c?t=2438… I thought this would be a cool feature to implement but showing backorders with impacted Sales Reps and their Accounts. I essentially did a copy and paste of his code, but modified the variables as well as adding «affected product». Unfortunately, it doesn’t work perfectly (some cases works) but a majority of the time I get this:

Error Message: MdxScript(Model) (17, 125) Calculation error in measure
‘Salesforce CC Backorder Report'[BackorderIQ]: A table of multiple
values was supplied where a single value was expected.

I get that it has to do with the fact that if I select a Sales rep, they may have multiple Products that are on backorder, so I tried variations in my maxBackorderForProduct variable (COUNT, COUNTA, COUNTX, COUNTAX, MAX, MAXX, MAXA)… so far MAXX is less likely to break it so I’m using that for now.

BackorderIQ =
VAR selectedRepName =
    FIRSTNONBLANK ( 'BOreport'[Sales Rep Name], TRUE )
VAR selectedActName =
    FIRSTNONBLANK ( 'BOreport'[Account Name], TRUE )
VAR selectedProduct =
    FIRSTNONBLANK ( 'BOreport'[Product ID], FALSE () )
VAR maxBackorderforSelectedAct =
    MAXX (
        SUMMARIZECOLUMNS (
            'BOreport'[Sales Rep Name],
            "Orders", SUM ( 'BOreport'[Back Order Qty] )
        ),
        [Orders]
    )
VAR maxBackorderforSelectedRep =
    MAXX (
        SUMMARIZECOLUMNS (
            'BOreport'[Account Name],
            "Orders", SUM ( 'BOreport'[Back Order Qty] )
        ),
        [Orders]
    )
VAR maxBackorderForProduct =
    MAXX (
        SUMMARIZECOLUMNS (
            'BOreport'[Product ID],
            "Orders", SUM ( 'BOreport'[Back Order Qty] )
        ),
        [Orders]
    )
RETURN
    IF (
        HASONEVALUE ( 'BOreport'[Sales Rep Name] ),
        selectedRepName & " has " & maxBackorderforSelectedRep & " backorders of " & selectedProduct & " at "
            & LOOKUPVALUE (
                'BOreport'[Account Name],
                'BOreport'[Back Order Qty], MAX ( 'BOreport'[Back Order Qty] )
            ),
        IF (
            HASONEVALUE ( 'BOreport'[Account Name] ),
            selectedActName & "'s has " & maxBackorderforSelectedAct & " backorders of " & selectedProduct & ", so give "
                & LOOKUPVALUE (
                    'BOreport'[Sales Rep Name],
                    'BOreport'[Back Order Qty], MAX ( 'BOreport'[Back Order Qty] )
                ) & " a heads up.",
            "Select a Product below, OR an Account or Sales Rep to the right."
        )
    )

I hope I can get the card to say the expected, «ACCOUNT2 has 50 backorders of ProductB, so give JOHN SMITH a heads up.» which is the max value they would get, because JOHN SMITH could also have 20 backorders of ProductA from Account1.

I’m creating a PowerBI dashboard for supply chain backorders. There was a great video I saw from Microsoft demonstrating a Dynamic message card on their Destiny dashboard showing players and their favorite weapon, https://youtu.be/uHP7YJH526c?t=2438… I thought this would be a cool feature to implement but showing backorders with impacted Sales Reps and their Accounts. I essentially did a copy and paste of his code, but modified the variables as well as adding «affected product». Unfortunately, it doesn’t work perfectly (some cases works) but a majority of the time I get this:

Error Message: MdxScript(Model) (17, 125) Calculation error in measure
‘Salesforce CC Backorder Report'[BackorderIQ]: A table of multiple
values was supplied where a single value was expected.

I get that it has to do with the fact that if I select a Sales rep, they may have multiple Products that are on backorder, so I tried variations in my maxBackorderForProduct variable (COUNT, COUNTA, COUNTX, COUNTAX, MAX, MAXX, MAXA)… so far MAXX is less likely to break it so I’m using that for now.

BackorderIQ =
VAR selectedRepName =
    FIRSTNONBLANK ( 'BOreport'[Sales Rep Name], TRUE )
VAR selectedActName =
    FIRSTNONBLANK ( 'BOreport'[Account Name], TRUE )
VAR selectedProduct =
    FIRSTNONBLANK ( 'BOreport'[Product ID], FALSE () )
VAR maxBackorderforSelectedAct =
    MAXX (
        SUMMARIZECOLUMNS (
            'BOreport'[Sales Rep Name],
            "Orders", SUM ( 'BOreport'[Back Order Qty] )
        ),
        [Orders]
    )
VAR maxBackorderforSelectedRep =
    MAXX (
        SUMMARIZECOLUMNS (
            'BOreport'[Account Name],
            "Orders", SUM ( 'BOreport'[Back Order Qty] )
        ),
        [Orders]
    )
VAR maxBackorderForProduct =
    MAXX (
        SUMMARIZECOLUMNS (
            'BOreport'[Product ID],
            "Orders", SUM ( 'BOreport'[Back Order Qty] )
        ),
        [Orders]
    )
RETURN
    IF (
        HASONEVALUE ( 'BOreport'[Sales Rep Name] ),
        selectedRepName & " has " & maxBackorderforSelectedRep & " backorders of " & selectedProduct & " at "
            & LOOKUPVALUE (
                'BOreport'[Account Name],
                'BOreport'[Back Order Qty], MAX ( 'BOreport'[Back Order Qty] )
            ),
        IF (
            HASONEVALUE ( 'BOreport'[Account Name] ),
            selectedActName & "'s has " & maxBackorderforSelectedAct & " backorders of " & selectedProduct & ", so give "
                & LOOKUPVALUE (
                    'BOreport'[Sales Rep Name],
                    'BOreport'[Back Order Qty], MAX ( 'BOreport'[Back Order Qty] )
                ) & " a heads up.",
            "Select a Product below, OR an Account or Sales Rep to the right."
        )
    )

I hope I can get the card to say the expected, «ACCOUNT2 has 50 backorders of ProductB, so give JOHN SMITH a heads up.» which is the max value they would get, because JOHN SMITH could also have 20 backorders of ProductA from Account1.

Я подозреваю, что ошибка вызвана этой строкой в ​​блоке HASONEVALUE (‘BOreport'[имя торгового представителя]):

selectedRepName & " has " & maxBackorderforSelectedRep & " backorders of " & selectedProduct & " at "

Этот код состоит из 3 элементов:
selectedRepName, maxBackorderforSelectedRep и selectedProduct

MaxBackorderforSelectedRep не может вызвать никаких проблем, поскольку всегда возвращает 1 значение.

SelectedRepName может вызвать проблемы (он возвращает несколько значений, если не выбран конкретный Rep), но формула защищает от такого сценария с помощью HASONEVALUE.

SelectedProduct похож на selectedRepName, поэтому он может возвращать несколько значений, если не выбран конкретный представитель, и у вас нет защиты от такого сценария. Отсюда ошибка.

Простой способ исправить это — добавить аналогичную защиту:

IF (
        HASONEVALUE ( 'BOreport'[Sales Rep Name] ) && 
        HASONEVALUE ( 'BOreport'[Product ID]),
        selectedRepName & " has " & maxBackorderforSelectedRep & " backorders of " & selectedProduct & " at "
            & LOOKUPVALUE (
                'BOreport'[Account Name],
                'BOreport'[Back Order Qty], MAX ( 'BOreport'[Back Order Qty] )
            ),

И та же логика должна применяться ко второму блоку:

  IF (
        HASONEVALUE ( 'BOreport'[Account Name] ) &&
        HASONEVALUE ( 'BOreport'[Product ID]),
        selectedActName & "'s has " & maxBackorderforSelectedAct & " backorders of " & selectedProduct & ", so give "
            & LOOKUPVALUE (
                'BOreport'[Sales Rep Name],
                'BOreport'[Back Order Qty], MAX ( 'BOreport'[Back Order Qty] )
            ) & " a heads up."

Это должно устранить ошибку. Однако я не могу гарантировать, что вы получите желаемое поведение — формула подразумевает, что вы должны выбрать конкретный Rep А ТАКЖЕ конкретного продукта, иначе он вернет сообщение по умолчанию.

LOOKUPVALUE(
      SearchTable[name]
    , SearchTable[id] -- how to get the fist value, if here are multiple id?
    , ThisTable[id]
    )

MyLookup = 
var LookupKey = ThisTable[Product]
return
CALCULATE (
    MAX ( SearchTable[Category] ),
    SearchTable[Product] = LookupKey
)

-- Bounty code
EVALUATE
ADDCOLUMNS (
    VALUES ( ThisTable[Product] ),
    "FilterLookup",
    VAR LookupKey = ThisTable[Product]
    RETURN
        CALCULATE ( MAX ( SearchTable[Category] ), SearchTable[Product] = LookupKey )
)

3 ответа

Лучший ответ

LookupFunction =
LOOKUPVALUE ( SearchTable[Category], SearchTable[Product], ThisTable[Product] )

MaxxLookup =
MAXX (
    FILTER ( SearchTable, SearchTable[Product] = ThisTable[Product] ),
    SearchTable[Category]
)

CaclculateMaxLookup =
CALCULATE (
    MAX ( SearchTable[Category] ),
    SearchTable[Product] = EARLIER ( ThisTable[Product] )
)

LookupMeasure =
LOOKUPVALUE (
    SearchTable[Category],
    SearchTable[Product], CALCULATE ( SELECTEDVALUE ( ThisTable[Product] ) )
)

MaxxMeasure=
VAR CurrProduct = CALCULATE ( SELECTEDVALUE ( ThisTable[Product] ) )
RETURN
    MAXX (
        FILTER ( SearchTable, SearchTable[Product] = CurrProduct ),
        SearchTable[Category]
    )

CaclculateMaxMeasure =
VAR CurrProduct = CALCULATE ( SELECTEDVALUE ( ThisTable[Product] ) )
RETURN
    CALCULATE ( MAX ( SearchTable[Category] ), SearchTable[Product] = CurrProduct )

Column =
VAR row_id = ThisTable[id] 
VAR ret_val =
    SELECTCOLUMNS (
        TOPN (
            1;
            CALCULATETABLE (
                SearchTable;
                SearchTable[id] = row_id;
                SearchTable[name] <> BLANK ()
            )
        );
        "name"; SearchTable[name]
    )
RETURN
    ret_val