Forum Discussion

gbarr12345's avatar
gbarr12345
Post Prodigy
2 years ago
Solved

Couldn't load the data for this visual error

Hi there,

 

I'm new to power bi and am trying to write a dax query that shows me that customers based in the north region that haven't purchased paper in the last 90 days.

I wrote the code for the measure and it saved ok when I pressed enter. However when I drag the measure out for a visual I'm getting the following error - MdxScript(Model) (4,1) Calculation error in measure 'Orders'[Customers from North without paper last 3 months]: A table of multiple values was supplied where a single value was expected.

 

How can I fix this error to show me the data I want to see? Or even is there a better dax query I could use to show what I'm looking for?

 

The code is as follows and screenshot of error also:

 

Customers from north Without Paper Last 3 Months =
VAR Last3Months = CALCULATE(MAX('DateTable'[Date]), DATEADD('DateTable'[Date], -3, MONTH))
VAR CustomerIDs =
    FILTER(
        VALUES('Orders'[Customer Name]),
        CALCULATE(
            COUNTROWS('Orders'),
            'Orders'[Region] = "North" && 'Orders'[Product Sub-Category] <> "Paper",
            'Orders'[Order Date] >= Last3Months
        ) = 0
    )
RETURN
SUMMARIZE(CustomerIDs, 'Orders'[Customer Name])

 

 

Need Help
  • vicky_'s avatar
    vicky_
    2 years ago

    The error message is telling you that you're trying to return a tabl, which isn't allowed (even if the table only has one row and column). The docs for the summarize page says that the return value is a table: https://learn.microsoft.com/en-us/dax/summarize-function-dax
    DAX measures only allow you to return a single value. 

    To fix this, you need to aggregate your CustomerIDs table - so something like:

    // ... your code here
RETURN
COUNTROWS(CustomerIDs)

    might be what you're after.

2 Replies

  • vicky_'s avatar
    vicky_
    Super User
    2 years ago

    The error message is telling you that you're trying to return a tabl, which isn't allowed (even if the table only has one row and column). The docs for the summarize page says that the return value is a table: https://learn.microsoft.com/en-us/dax/summarize-function-dax
    DAX measures only allow you to return a single value. 

    To fix this, you need to aggregate your CustomerIDs table - so something like:

    // ... your code here
RETURN
COUNTROWS(CustomerIDs)

    might be what you're after.