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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Super User
Super User

@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!:
Power BI Cookbook Third Edition (Color)

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 ?

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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