Forum Discussion
DISTINCTCOUNT excluding certain records
Hello. I need some help.
Below is an example of my model:
I am trying to get a count of all transactions with a First Transaction Date before 01/02/2022 (based on slicer selection) using:
CALCULATE(DISTINCTCOUNT('Transaction Table'[Rreference]),
FILTER('Transaction Table','Transaction Table'[First Transaction Date]<MIN('Date Table'[Date].[Date])
However, the above gives me a count of 2, instead of 3, omitting reference 167 because it doesn’t have a transaction date in the month of February 2022.
Any help will be much appreciated.
Thanks
Hi Anonymous
Please try= CALCULATE ( DISTINCTCOUNT ( 'Transaction Table'[Rreference] ), 'Transaction Table'[First Transaction Date] < MIN ( 'Date Table'[Date] ), CROSSFILTER ( 'Transaction Table'[Transaction Date], 'Date Table'[Date], NONE ) )
8 Replies
- tamerj1
Community Champion
Hi Anonymous
Please try= CALCULATE ( DISTINCTCOUNT ( 'Transaction Table'[Rreference] ), 'Transaction Table'[First Transaction Date] < MIN ( 'Date Table'[Date] ), CROSSFILTER ( 'Transaction Table'[Transaction Date], 'Date Table'[Date], NONE ) ) - SpartaBI
Anonymous please try:
CALCULATE(DISTINCTCOUNT('Transaction Table'[Rreference]),
'Transaction Table'[First Transaction Date]<MIN('Date Table'[Date].[Date])
)
In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.
SpartaBI Logo
Visit SpartaBI website Visit SpartaBI Linkdin Visit SpartaBI Facebook
Showcase Report – Contoso By SpartaBI- SpartaBI
Anonymous ok, no worries 🙂 will get there.
Can you maybe share the file / sample of the file with fake data and explain there in a text box the desired output you want to get.
