Filtered Distinct Count with CALCULATE not working as intended

[DTN721]

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:

CustomerID	Login Date	Country	Level	Highest Level
ABC	10/10/2021	UK	1	2
ABC	10/10/2021	UK	1	2
ABC	10/10/2021	UK	1	2
ABC	11/10/2021	UK	2	2
ABC	12/10/2021	UK	2	2
DEF	10/10/2021	France	1	3
DEF	11/10/2021	France	2	3
DEF	11/10/2021	France	2	3
DEF	12/10/2021	France	3	3
GHI	12/10/2021	UK	1	3
GHI	12/10/2021	UK	1	3
GHI	12/10/2021	UK	1	3
GHI	15/10/2021	UK	1	3
GHI	17/10/2021	UK	2	3
GHI	19/10/2021	UK	3	3

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
ABC	1	2	0
DEF	1	1	1
GHI	2	1	2

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
ABC	3	2	0
DEF	1	2	1
GHI	4	1	1

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

[DTN721]

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

[Fsciencetech]

@DTN721 

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

[amitchandak · Accepted solution]

@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" ))