Difference in date filters

[Anonymous]

I have a data set with customer ID, Type and enrolment date. I am trying to calculate the no of customers in a particulat category/type  using a measure as some of the ID's have duplicates. 

measure 1 = CALCULATE(DISTINCTCOUNT('CustomerTable'[Customer_ID]), CustomerTable[Type] = "Local")

Since I'm using many other tables I have created a relationship between the DateTable and the CustomerTable with a one to many relationship. However, on cross examining the results for measure 1, I found a higher result on a table with a 'Date' column from the DateTable. The relationship may have created more records than expected, since when I use the Enrolment date the numbers are lower and are the exact numbers. 

How can I get the actual number for the measure above? I have tried adding functions such as HASONEFILTER, ALLSELECTED. I want the measure to return values only for the enrolment date when used with the DateTable.

Please help! 

Sample of the data 

Customer ID	Type	Date enroled
1	Local	12/12/22
2	International	13/12/22
1	Local	14/12/22
4	International	15/12/22
1	Local	16/12/22
6	International	17/12/22
7	Local	18/12/22
8	International	19/12/22
1	Local	20/12/22
10	International	21/12/22
11	Local	22/12/22
12	International	23/12/22
13	Local	24/12/22

[v-cazheng-msft · Accepted solution]

Hi @Anonymous 

Maybe you can try this Measure.

CountCxByType =

VAR midVal_ =

    CALCULATE (

        MIN ( CustomerTable[Date enroled ] ),

        FILTER (

            ALL ( CustomerTable ),

            CustomerTable[Customer ID ] = MAX ( CustomerTable[Customer ID ] )

        )

    )

RETURN

    COUNTX (

        FILTER ( CustomerTable, CustomerTable[Date enroled ] = midVal_ && CustomerTable[Type] = "Local"),

        CustomerTable[Customer ID ]

    )

Then, the result should look like this.

Also, attached the pbix file as reference.

Best Regards,

Community Support Team _ Caiyun

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

[Greg_Deckler]

@Anonymous Perhaps:

measure 1 =
  VAR __Table = SUMMARIZE('CustomerTable',[Customer ID],[Type],"__DateEnrolled",MAX('CustomerTable'[Date enrolled]))
RETURN
  COUNTROWS(__Table,[Type]="Local")

[amitchandak]

@Anonymous , First Try like this

measure 1 = CALCULATE(DISTINCTCOUNT('CustomerTable'[Customer_ID]), Filter(CustomerTable, CustomerTable[Type] = "Local"))

In DISTINCTCOUNT date-wise and grand total can have differences.

Can you share what you are getting vs what is expected ?