topic Re: DAX: Count the number of unique IDs with the last status per period in DAX Commands and Tips

DAX: Count the number of unique IDs with the last status per period

EvaPBI — Thu, 12 Dec 2024 17:30:20 GMT

Hi everyone,

I thought this problem was easier to solve, but for some reason, I can't get the exact expected results and the solutions on the forum aren't exactly applicable.

Here is my main table :

ID	PERIOD	DATE	STATUS
1	2024-02-01	2024-02-01	A
1	2024-06-01	2024-06-20	A
2	2024-04-01	2024-04-14	B
2	2024-05-01	2024-05-12	A
2	2024-06-01	2024-06-10	A
2	2024-06-01	2024-06-18	B
3	2024-09-01	2024-09-03	B
3	2024-09-01	2024-09-07	A
4	2024-09-01	2024-09-28	B
5	2024-03-01	2024-03-11	B
5	2024-05-01	2024-05-16	A
5	2024-07-01	2024-07-21	B
6	2024-08-01	2024-08-08	B
6	2024-11-01	2024-11-17	A
7	2024-02-01	2024-02-28	B
7	2024-05-01	2024-05-05	B
7	2024-06-01	2024-06-21	B
7	2024-10-01	2024-10-27	A
8	2024-03-01	2024-03-15	B
8	2024-03-01	2024-03-22	A
9	2024-10-01	2024-10-22	B
9	2024-11-01	2024-11-09	B
8	2024-07-01	2024-07-25	B
10	2024-04-01	2024-04-07	A
10	2024-11-01	2024-11-14	B
10	2024-07-01	2024-07-26	A

With DAX, I want to count the number of unique IDs (based on the last status) per period (cumulatively) to create a graph showing the status changes.

Here are the expected results:

PERIODE	STATUT A	STATUT B
2024-02-01	1	1
2024-03-01	2	2
2024-04-01	3	3
2024-05-01	5	1
2024-06-01	4	2
2024-07-01	2	4
2024-08-01	2	5
2024-09-01	3	6
2024-10-01	4	6
2024-11-01	4	6

For example, Period 2024-04-01 has
3 "A" Status (ID: 1, 8, 10) and
3 "B" Status (ID: 2, 5, 7).

I tried to create a first measure that identifies the lastest_status for the appropriate period (cumulatively) but the measure always identifies the last status (for period 2024-04-01, it would falsely classify ID 8 with a "B" Status). Note that it may not be the right way to start.

Thank you very much for any help or advice.
Best Regards,

Eva

Re: DAX: Count the number of unique IDs with the last status per period

lbendlin — Thu, 12 Dec 2024 20:45:35 GMT

I don't see any status changes for ID 1?

Re: DAX: Count the number of unique IDs with the last status per period

EvaPBI — Thu, 12 Dec 2024 21:36:16 GMT

Correct. No status change for ID 1.
The measures (STATUS_A_COUNT & STATUS_B_COUNT) should count the number of unique ID in each status per period.
As ID 1 Status didn't change, I expect it to be counted in the column STATUS_A for every period (see expected results table).
Thank you for the question, I didn't specify that 🙂

Re: DAX: Count the number of unique IDs with the last status per period

lbendlin — Thu, 12 Dec 2024 22:04:07 GMT

Like this?

Re: DAX: Count the number of unique IDs with the last status per period

EvaPBI — Fri, 13 Dec 2024 19:33:56 GMT

Thank you very much !
The measure's behavior seems perfect. As I have a lot of IDs, the end goal is to show the number of status A and B per period. I try to apply a distinctcount on the measure and a filter on the status (=A and = B) but it didn't work. This is the expected result :

Re: DAX: Count the number of unique IDs with the last status per period

lbendlin — Fri, 13 Dec 2024 22:24:46 GMT

Re: DAX: Count the number of unique IDs with the last status per period

EvaPBI — Wed, 18 Dec 2024 13:15:39 GMT

Very clever, works perfectly !

Thank you for your time.