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.

Answer 1

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: