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

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.

1 Answer

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: