Distinct count - multiple filters

[lin_charlotte]

Dear all!

I have a database with multiple columns, which for my issue only 3 are relevant:

- Reference document number

- Date

- Quantities sold.

I want to be able to write a DAX formulas that would count the MTD distinct counts of reference document number for which quantities are positive (i.e. it's not a refund).

I started with this formula:

Transactions_CY_MTD =

TOTALMTD(calculate(Distinctcount('KE30'[Reference document]),filter(KE30[quantities],">"&0)),'Date Table'[Date])

But i get an error message saying that "A single value for column 'quantities' in table 'ke30' cannot be determined. This can happen when a formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Uploading below a simplified version of my datasource - the name of the table in my datamodel of the source is "KE30"

thanks in advance for the help!! 

[v-luwang-msft · Accepted solution]

Hi @lin_charlotte ,

Test the below:

Transactions_CY_MTD = 
TOTALMTD (
    CALCULATE (
        DISTINCTCOUNT ( 'KE31'[Reference document] ),
        FILTER ( KE31,[quantities] >0 )
    ),
    'Date Table'[Date]
)

Output result:

If not incorrect ,could you pls show me what output you want?

Best Regards

Lucien

[lin_charlotte]

Thanks! it worked 🙂

[lbendlin]

You are trying to do a MTD calculation but your sample data only has single values for each month.  That is not sufficient to do MTD calculations.

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

[lin_charlotte]

Hi Ibendlin,

Thanks for answering, 

My dataset looks like this (I don't seem to be able to add an attachment) - i have daily postings of quantities, by Customer with a reference document. I would like to have the distinct count of reference documents for which the quantity is positive (i.e. not a refund), by period (hence the MTD), so that i can then replicate the MTD Distinct Count to Last Year MTD Distinct Count and Last Last Year Distinct Count. 

Thanks for the help!

Posting date	Customer	Reel SALES QTIES ALL PROD	Reference document
1/2/2019	A	1	770782093
1/2/2019	A	1	770782028
1/2/2019	A	1	770782087
1/2/2019	A	1	770782062
1/2/2019	A	1	770782025
1/2/2019	A	1	770782026
1/2/2019	A	1	770782063
1/2/2019	A	1	770782022
1/2/2019	A	1	770782047
1/2/2019	A	1	770782046
1/2/2019	A	1	770782023
1/2/2019	A	1	770782045
1/2/2019	A	1	770782027
1/2/2019	A	-1	770782030
1/2/2019	A	1	770782090
1/2/2019	A	-1	770782070
1/2/2019	A	1	770782091
1/2/2019	A	1	770782092
1/2/2019	A	1	770782021
1/2/2019	A	1	770782073
1/2/2019	A	-1	770782057
1/2/2019	A	1	770782064
1/2/2019	A	-1	770782056
1/2/2019	A	1	770782024
1/2/2019	A	-1	770782029
1/2/2019	A	1	770782093
1/2/2019	A	1	770782089
1/2/2019	A	-1	770782069
1/2/2019	A	1	770782093
1/2/2019	A	1	770782088
1/2/2019	D	1	770782016
1/2/2019	D	1	770782059
1/2/2019	D	1	770782018
1/2/2019	D	1	770782059
1/2/2019	D	1	770782015
1/2/2019	D	1	770782017