How to fix Multiple Columns Cannot be Converted to a Scalar Value in DAX

Question

I am trying to use the DAX query below to get the distinct daily count of customerID and filter out only when a stock was taken by the customer.

Distinct CID = 

var _table = 
SUMMARIZE(
    'Secondary Meetings',
    'Secondary Meetings'[Createddate].[Day],
    "Distinct", DISTINCTCOUNT('Secondary Meetings'[CustomerID]),
    "Stock Take", 
    FILTER(
        'Secondary Meetings',
        'Secondary Meetings'[StockTake]="Yes"
    ) 

    return SUMX(_table,[Distinct])

When I try the above DAX function I get this error below:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

You have provided a TABLE for calculating column "Stock Take" which is not allowed. Define appropriate aggregation for the column "Stock Take" based on one single source column. If things are not clear, please prove your sample data with your expected output from them. — mkRabbani

Angelo Canepa Angelo Canepa · Accepted Answer · 2020-09-25T11:53:36

Assuming your table looks like this, with multiple CustomerID for each date.

+-----------------+------------+-----------+
|       Date      | CustomerID | StockTake |
+-----------------+------------+-----------+
| 01 January 2020 | 1          | Yes       |
+-----------------+------------+-----------+
| 01 January 2020 | 1          | Yes       |
+-----------------+------------+-----------+
| 01 January 2020 | 4          | No        |
+-----------------+------------+-----------+
| 01 January 2020 | 4          | No        |
+-----------------+------------+-----------+
| 01 January 2020 | 3          | Yes       |
+-----------------+------------+-----------+
| 02 January 2020 | 2          | No        |
+-----------------+------------+-----------+
| 02 January 2020 | 1          | Yes       |
+-----------------+------------+-----------+
| 02 January 2020 | 2          | No        |
+-----------------+------------+-----------+

The calculation below uses an iterator SUMX to get the distinct count for each day and later summing the results of each evaluation.

Distinct CID = 
SUMX (
    VALUES ( 'Table'[Date] ),
    CALCULATE ( DISTINCTCOUNT ( 'Table'[CustomerID] ),
    'Table'[StockTake]="Yes" )
)

The output:

How to fix Multiple Columns Cannot be Converted to a Scalar Value in DAX

1 Answers