Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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 )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi. This is not working unfortunately.

      • SpartaBI's avatar
        SpartaBI
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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.