cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DTN721
Regular Visitor

Filtered Distinct Count with CALCULATE not working as intended

Hi,

 

I have one table named Query1 in my Power BI report which has about 4 million rows of data and it looks a bit like this:

CustomerIDLogin DateCountryLevelHighest Level
ABC10/10/2021UK12
ABC10/10/2021UK12
ABC10/10/2021UK12
ABC11/10/2021UK22
ABC12/10/2021UK22
DEF10/10/2021France13
DEF11/10/2021France23
DEF11/10/2021France23
DEF12/10/2021France33
GHI12/10/2021UK13
GHI12/10/2021UK13
GHI12/10/2021UK13
GHI15/10/2021UK13
GHI17/10/2021UK23
GHI19/10/2021UK33

 

I want to write 3 DAX measures to count the distinct number of Login dates for each customer to find out how many days they have spent on each level.

 

So that the desirable outcome when using the measures for a table would look something like this:

 Total Days Spent on Level 1

Total Days Spent on Level 2

Total Days Spent on Level 3
ABC120
DEF111
GHI212

 

I've tried this to calculate the desirable measures:

Days Spent on L1 = CALCULATE( DISTINCTCOUNT (Query1[Login Date] ), Level = "1" ) 

Days Spent on L2 = CALCULATE( DISTINCTCOUNT (Query1[Login Date] ), Level = "2" ) 

Days Spent on L3 = CALCULATE( DISTINCTCOUNT (Query1[Login Date] ), Level = "3" ) 

 

However, rather than doing a DISTINCTCOUNT on the Login Dates, it's counting all the duplicate Login Dates and the table looks like this:

 Total Days Spent on Level 1

Total Days Spent on Level 2

Total Days Spent on Level 3
ABC320
DEF121
GHI411

 

Any help on this is very much appreciated it. Thanks in advance! 🙂

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@DTN721 , Please check date does not have any timestamp. Change datatype to datetime and choose a format with time and check. If there is a timestamp, then create date only column

 


Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))

 

 

also, try with filter

Days Spent on L1 = CALCULATE( DISTINCTCOUNT (Query1[Login Date] ), filter( Query1, Query[ Level] = "1" ))

Days Spent on L2 = CALCULATE( DISTINCTCOUNT (Query1[Login Date] ), filter( Query1, Query[ Level] = "2" ))

Days Spent on L3 = CALCULATE( DISTINCTCOUNT (Query1[Login Date] ), filter( Query1, Query[ Level] = "3" ))



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

View solution in original post

3 REPLIES 3
DTN721
Regular Visitor

@Fsciencetech  @amitchandak  Thank you both so much for your time! Your suggestions worked 😊

Fsciencetech
Helper III
Helper III

@DTN721 

Noting issue in your measure, but first check your Logindate column, Is this is date datatype?, and also remove timestamp.

amitchandak
Super User
Super User

@DTN721 , Please check date does not have any timestamp. Change datatype to datetime and choose a format with time and check. If there is a timestamp, then create date only column

 


Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))

 

 

also, try with filter

Days Spent on L1 = CALCULATE( DISTINCTCOUNT (Query1[Login Date] ), filter( Query1, Query[ Level] = "1" ))

Days Spent on L2 = CALCULATE( DISTINCTCOUNT (Query1[Login Date] ), filter( Query1, Query[ Level] = "2" ))

Days Spent on L3 = CALCULATE( DISTINCTCOUNT (Query1[Login Date] ), filter( Query1, Query[ Level] = "3" ))



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors