Couldn't load the data for this visual error

[gbarr12345]

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

[gbarr12345]

Thank you very much!

[vicky_ · Accepted solution]

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.