cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lin_charlotte
Frequent Visitor

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"
lin_charlotte_0-1636973794036.png

 

thanks in advance for the help!! 

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @lin_charlotte ,

Test the below:

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

Output result:

vluwangmsft_0-1637303548664.png

 

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

 

 

Best Regards

Lucien

View solution in original post

4 REPLIES 4
v-luwang-msft
Community Support
Community Support

Hi @lin_charlotte ,

Test the below:

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

Output result:

vluwangmsft_0-1637303548664.png

 

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

 

 

Best Regards

Lucien

Thanks! it worked 🙂

lbendlin
Super User
Super User

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.

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!