cancel
Showing results for
Did you mean:
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:

 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! 🙂

1 ACCEPTED SOLUTION
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 !!
3 REPLIES 3
Regular Visitor

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

Helper III

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

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 !!

Announcements