Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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! 🙂
Solved! Go to 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" ))
@Fsciencetech @amitchandak Thank you both so much for your time! Your suggestions worked 😊
Noting issue in your measure, but first check your Logindate column, Is this is date datatype?, and also remove timestamp.
@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" ))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |