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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
aabi
Helper I
Helper I

Counting Unique Values Level based on last timestamp

Hello All

 

I have been trying but with no effect to count a the numbers of users that are on a certain level. But only the MAX of a certain time stamp

 

As a sample the table below

 

CustomerIDTimestampLevel
11111/1/2023Level 1
11111/3/2023Level 1
111110/18/2023Level 2
22221/2/2023Level 1
22221/3/2023Level 1
22221/4/2023Level 1
33335/6/2023Level 1
33335/7/2023Level 2
33335/8/2023Level 3
44443/3/2023Level 4
44443/4/2023Level 4
44443/5/2023Level 5
55555/5/2023Level 1
55555/6/2023Level 3
55555/7/2023Level 2

 

a measure for each level to count the latest status of a user without double counting multile levels.

By using the a distinct count, as I do not take into consideration the date

Level 1 Count = CALCULATE(DISTINCTCOUNT(Table[CustomerID]),FILTER(Table,Table[Points]= " Level 1"))
I'm double counting Level 1 for all unique customerID
 
When I try to put in the date, I only get as a result 1 (as it filters all dates and keeps only the max date?)
Example that I use 
 
Level 1 Count = 
VAR MaxDate =
    MAX ( Table[Timestamp] )
VAR _STATUS =
    FILTER (
        Table,
        Table[Level] = Level 1
            && Table[Timestamp] = MaxDate
    )
VAR _Count =
    COUNTROWS (DISTINCT( _STATUS ))
RETURN
    _Count
 
It should be noted that is possible for me to get the above numbers, by adjusting the table that I'm counting and sorting by date and then removing all duplicates (customerID), so I effectively have only 1 entry per customerid(and is the latest). But in this case I cannot use a slicer for the dates
 
Anyones has any idea how to make this measure?
 
 
1 ACCEPTED SOLUTION

Hi @aabi please create another column to check true false last date for customer

True False LastDateForCustomer = Sheet1[MaxDatePerCutomer]=Sheet1[Timestamp].
After that create overvies as shown on Output (slice True)

Did I answer your question? Kudos appreciated / accept solution!

 

some_bih_0-1693472757319.png

 

Output

some_bih_1-1693472803597.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






View solution in original post

9 REPLIES 9
some_bih
Super User
Super User

Hi @aabi as @TomMartens wrote what is expected output.Below I created cal. column from your picture for MaxDatePerCustomer.  In your data same each customer at some date have only one Level ID, no two Level ID's at same date so countidistinct always return 1

Hope this help, kudos appreciated.

 

MaxDatePerCutomer =
VAR __max=MAX(Sheet1[Timestamp])
VAR _customer=Sheet1[CustomerID]
VAR __result=
    CALCULATE(
    MAX(Sheet1[Timestamp]),
    FILTER(ALL(Sheet1),Sheet1[CustomerID]=_customer)
    )
RETURN __result

 

some_bih_0-1693376491575.png

 

some_bih_1-1693376634760.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Hi @some_bih 

 

Thank you for your input, however that that does not really help.

 

Let me explain a bit more what I need.. first of all the dataset I have has around 20K users. From 1 timestamp up to 10+ timestamps, and someone can start from level 1 and become level 5.

 

The endresults will look as follows :

 

CountryLevel 1Level 2Level 3Level 4Level 5Column Total
United Kingdom100050020050012343434
United States500300100100032545154
Japan30020030010065497449
Germany2001001005123947715000
Rows Total2000110070067232051431037

 

The country exist in another table (unique based on customerID, so that not an issue)

 

Level 1,2,3,4,5 and Total - Columns would be DAX measures
Column Total = Level 1 + 2+3+4+5
Rows Total = Level Dax Formula count

Espected results based on dataset that I have posted

CountryLevel 1Level 2Level 3Level 4Level 5Total
XXXX121015
Total121015


Where "Level 1" = 1 due to 

 

CustomerIDTimestampLevel
222201/02/2023Level 1
222201/03/2023Level 1
222201/04/2023Level 1

 

Level 2 = 2

 

CustomerIDTimestampLevel
111101/01/2023Level 1
111101/03/2023Level 1
111110/18/2023Level 2
555505/05/2023Level 1
555505/06/2023Level 3
555505/07/2023Level 2

 

Level 3 = 1

 

CustomerIDTimestampLevel
333305/06/2023Level 1
333305/07/2023Level 2
333305/08/2023Level 3

 

Level 4 = 0


As there is no-one level 4 last date.

Level 5 = 1

 

CustomerIDTimestampLevel
444403/03/2023Level 4
444403/04/2023Level 4
444403/05/2023Level 5

 

Hi @aabi I do not fully understand your logic, let me explain why not.

In your data, format of date is DD/MM/YYYY?

For Level 1 I have that Max date per level is as shown on picture below for Cal. column MaxDatePerLevel

 
Question for Level 4: how that for Level 4 you expect 0 when are some customer and dates?
some_bih_2-1693463001827.png

MaxDatePerLevel for Level 2 is as below

some_bih_1-1693462882413.png

 

MaxDatePerLevel =
VAR __max=MAX(Sheet1[Timestamp])
VAR __level=Sheet1[Level]
VAR __result=
    CALCULATE(
    MAX(Sheet1[Timestamp]),
    FILTER(ALL(Sheet1),Sheet1[Level]=__level)
    )
RETURN __result

some_bih_0-1693461993508.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Hi @some_bih 

 

In my actual data is a timestamp dd/mm/yyyy hh:mm:ss.

In the sample I meant to have dd/mm/yyyy, but from what I see now the I have 1 not valid date for CustomerID 1111 as 10/18/2023 instead of 18/10/2023. The rest are ok.

 

For Level 4 is 0 because on the last entry(timestamp) of customerID 4444 he is on Level 5.

 

I believe my main issue here is that not the actual count, but how to make make a summarize table with only the last date for each customer ID as follows :

CustomerIDTimestampLevel
111118/10/2023Level 2
222201/04/2023Level 1
333305/08/2023Level 3
444403/05/2023Level 5
555505/07/2023Level 2

 

If I have this table (as a temporary table in a measure), the count part is simple.


Also thank you, I think you gave me an idea now...
If I put the maxdate in the summary table then I might be able to work out the max/latest level per customer....

Hi @aabi please create another column to check true false last date for customer

True False LastDateForCustomer = Sheet1[MaxDatePerCutomer]=Sheet1[Timestamp].
After that create overvies as shown on Output (slice True)

Did I answer your question? Kudos appreciated / accept solution!

 

some_bih_0-1693472757319.png

 

Output

some_bih_1-1693472803597.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Hi @aabi 

If this is solution, please accept it so other member of community could use it. Thank you





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Hi @some_bih 

 

While what you made kind of works, but is not something that I can use on my model.

However with your assistance/inspiration, I have managed to create the formula that I need as follows :

Level 1 Count = VAR LatestTimestampPerCustomer =
ADDCOLUMNS(
SUMMARIZE(
'Table1',
'Table1'[CustomerID],
"MaxTimestamp", MAX('Table1'[Timestamp])
),
"LatestLevel",
CALCULATE(
MAX('Table1'[Level]),
FILTER(
'Table1',
'Table1'[CustomerID] = EARLIER('Table1'[CustomerID]) &&
'Table1'[Timestamp] = EARLIER([MaxTimestamp])
)
)
)
RETURN
COUNTROWS(
FILTER(
LatestTimestampPerCustomer,
[LatestLevel] = "Level 1"
)
)

 

I just need to change the "Level 1" to "Level 2" on the countrows for each level. Results will be as follows :

 

Level 1Level 2Level 3Level 4Level 5
12101

 

TomMartens
Super User
Super User

Hey @aabi ,

 

what is the expected result based on the sample data you provided?

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @TomMartens 

 

Well my sample might not be the best, but the results should have been 

Level 1 Count = 1

Only UserID 2222, is level 1 on the last date.

I would also have the same formula as "level 2 count" which should be 2 (UserID 1111 and 5555)

 

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Users online (958)