Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Difference in date filters

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 TypeDate enroled 
1Local 12/12/22
2International13/12/22
1Local 14/12/22
4International15/12/22
1Local 16/12/22
6International17/12/22
7Local 18/12/22
8International19/12/22
1Local 20/12/22
10International21/12/22
11Local 22/12/22
12International23/12/22
13Local 24/12/22
1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

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.

vcazhengmsft_0-1643698684753.png

 

vcazhengmsft_1-1643698684756.png

 

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!

 

View solution in original post

3 REPLIES 3
v-cazheng-msft
Community Support
Community Support

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.

vcazhengmsft_0-1643698684753.png

 

vcazhengmsft_1-1643698684756.png

 

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
Community Champion
Community Champion

@Anonymous Perhaps:

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@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 ?

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.