topic Re: Count of users at each status at a specific date in DAX Commands and Tips

Count of users at each status at a specific date

Monepieveloce — Wed, 10 Jul 2024 10:07:40 GMT

Hello All!
I have a table with columns username, current status and update date. In this table all the changes of status are recorded.
I need to calculate the count of users at each status and filter it by month, so when then month filter is applied the calculation should show the outcome at the end of that month.
Sample table:

Username	Current Status	Update Date
AAA	1	1/May/24
BBB	1	1/May/24
CCC	2	1/May/24
DDD	3	1/May/24
EEE	4	1/May/24
AAA	2	1/Jun/24
FFF	1	1/Jun/24
BBB	2	1/Jun/24
CCC	2	1/Jun/24
AAA	1	1/Jul/24
GGG	2	1/Jul/24
AAA	4	1/Aug/24

Sample expected result:

May filter applied
Status	# Count
1	2
2	1
3	1
4	1

June filter applied
Status	# Count
1	1
2	3
3	1
4	1

July filter applied
Status	# Count
1	2
2	3
3	1
4	1

August filter applied
Status	# Count
1	1
2	3
3	1
4	2

Anyone can help?

Thank you!

Re: Count of users at each status at a specific date

BeaBF — Wed, 10 Jul 2024 10:36:02 GMT

Monepieveloce Hi! You don't need any calculation, you have to construct the table in this way:

you'll obtain a table with the Status and the count of Users for each Status and you can easily filter the table by a slicer on month, like:

But, if you want a measure, you can use:

UsersCount = CALCULATE(DISTINCTCOUNT(SampleTable[Username]), ALLEXCEPT(SampleTable, SampleTable[Current Status], SampleTable[Update Date]))

BBF

Re: Count of users at each status at a specific date

Monepieveloce — Wed, 10 Jul 2024 10:43:07 GMT

Thanks for looking into the issue!
I tried the proposed solution, but I got the count of users at each level only for those users who had an update on the specific month, while users that were at the same status from a previous month were not considered 😞

Re: Count of users at each status at a specific date

BeaBF — Wed, 10 Jul 2024 10:55:37 GMT

Monepieveloce oh sorry, i didn't undestand. But, based on your example, i can't understand what do you add for each month. can you explain me how do you reach the output of july?

July filter applied
Status	# Count
1	2
2	3
3	1
4	1

Re: Count of users at each status at a specific date

Monepieveloce — Wed, 10 Jul 2024 11:04:27 GMT

Thanks you for the reply!
The status of each user in July is the following, so that is why I get the july outout posted above :

User	Status in July	Last change
AAA	1	1/Jul/24
BBB	2	1/Jun/24
CCC	2	1/Jun/24
DDD	3	1/May/24
EEE	4	1/May/24
FFF	1	1/Jun/24
GGG	2	1/Jul/24

Re: Count of users at each status at a specific date

Monepieveloce — Wed, 10 Jul 2024 13:35:18 GMT

Anyone has an idea to solve this please? Thanks!

Re: Count of users at each status at a specific date

foodd — Wed, 10 Jul 2024 13:38:09 GMT

This reply is informational. Please follow the decorum of the Community Forum when asking a question.

Please share your work-in-progress Power BI Desktop file (with sensitive information removed) and any source files in Excel format that fully address your issue or question in a usable format (not as a screenshot). You can upload these files to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then provide the file's URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

Re: Count of users at each status at a specific date

Monepieveloce — Wed, 10 Jul 2024 14:16:34 GMT

foodd Thank you for the guidelines!
I have shared the sample file here: https://we.tl/t-YcGOXa0Qm6
I have added in the file how I would like to visualize the data (column chart + month slicer)
Thank you in advance for the help!

Re: Count of users at each status at a specific date

BeaBF — Wed, 10 Jul 2024 14:34:24 GMT

Monepieveloce

1. Create a separate table for Dates, in my case i named it DateSampleTable, with this code:

DateSampleTable = DISTINCT(SampleTable[Update Date])

2. Create the measure:

CountByUsernameStatus =

VAR VirtualTable =

CALCULATETABLE(SUMMARIZE(FILTER('SampleTable', SampleTable[Update Date] <= SELECTEDVALUE(DateSampleTable[Update Date])), SampleTable[Username], "Date", MAX(SampleTable[Update Date]), "STATUS", MAX(SampleTable[Current Status])))

RETURN

COUNTROWS(VirtualTable)

Try it and let me know.

BBF

Re: Count of users at each status at a specific date

Monepieveloce — Thu, 11 Jul 2024 06:58:23 GMT

BeaBF thanks for the answer!
Unfortunately it did not work, since the formula counts the users per status but shows only the users that had an update in the month selected in the slicer, so it does not take into consideration what happened before the month selected.
What I was able to achieve is the cumulative count of users at each month (but I was not able to show the categorization by status), or the count of users at each status who had an update on that month. I could not find a way to combine the 2 and get the cumulative count of users at each status at the end of the month.

Re: Count of users at each status at a specific date

BeaBF — Thu, 11 Jul 2024 07:15:08 GMT

Monepieveloce the slicer must be an external table, did you do it?

BBF

Re: Count of users at each status at a specific date

Anonymous — Thu, 11 Jul 2024 09:26:50 GMT

Hi all,thanks for the quick reply, I'll add more.

Hi Monepieveloce ,

Please follow these steps:

1.Deleting a relationship between tables.

2.Use the following DAX expression to create a table

Table = VALUES(UsersTable[Current Status])

3.Use the following DAX expression to create a measure

Measure = VAR _dateForSlicer = MIN('DateTable'[Date].[Date]) VAR _status = SELECTEDVALUE('Table'[Current Status]) VAR _table = SUMMARIZE(FILTER('UsersTable',[Update Date] <= _dateForSlicer),[Username],"Update Date",MAX('UsersTable'[Update Date])) VAR _table2 = ADDCOLUMNS(_table,"Current Status",LOOKUPVALUE(UsersTable[Current Status],UsersTable[Username],[Username],UsersTable[Update Date],[Update Date])) VAR _result = COUNTROWS(FILTER(_table2,[Current Status] = _status)) RETURN _result

4.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count of users at each status at a specific date

Monepieveloce — Thu, 11 Jul 2024 09:35:13 GMT

BeaBF
Yes I did so but it did not work, I believe I followed the same steps but apparently I did something wrong 😞

In the meanwhile I created a table printing the status of each user at the end of each month and I got what I need, but I am afraid this table may slow down the performance