Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Distinct count - multiple filters

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!! 

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi Anonymous ,

    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

4 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks! it worked 🙂

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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 dateCustomerReel SALES QTIES ALL PRODReference document
      1/2/2019A1770782093
      1/2/2019A1770782028
      1/2/2019A1770782087
      1/2/2019A1770782062
      1/2/2019A1770782025
      1/2/2019A1770782026
      1/2/2019A1770782063
      1/2/2019A1770782022
      1/2/2019A1770782047
      1/2/2019A1770782046
      1/2/2019A1770782023
      1/2/2019A1770782045
      1/2/2019A1770782027
      1/2/2019A-1770782030
      1/2/2019A1770782090
      1/2/2019A-1770782070
      1/2/2019A1770782091
      1/2/2019A1770782092
      1/2/2019A1770782021
      1/2/2019A1770782073
      1/2/2019A-1770782057
      1/2/2019A1770782064
      1/2/2019A-1770782056
      1/2/2019A1770782024
      1/2/2019A-1770782029
      1/2/2019A1770782093
      1/2/2019A1770782089
      1/2/2019A-1770782069
      1/2/2019A1770782093
      1/2/2019A1770782088
      1/2/2019D1770782016
      1/2/2019D1770782059
      1/2/2019D1770782018
      1/2/2019D1770782059
      1/2/2019D1770782015
      1/2/2019D1770782017