Summarize the Measure output

[chetanpatel14]

In Power Bi, I have a 'public dailyusagereport' with 2 columns called "Name" and "LastLoginDate". My input table as data from Aug 2023 to 6th Nov2024

1.I have provided the date range in the slicer of the dashboard, which shows the dates from Aug 2023 till 6th Nov 2024. As soon as the user selects the dates I am creating the new intermediate table named DateFilteredDailyUsageReport based on the selected date range.
2. I calculated how many times users are loggedin in Past X days within the date range. my output is coming as below as expected
Name LoginCount
0520177 20
0539179 20
0524535 20
0526162 20
0563914 19
0577696 19
0558652 19
0583882 19
0583503 19

I achieved this with the following simple measure
LoginCountPerUserMeasure =
CALCULATE(
COUNTROWS('DateFilteredDailyUsageReport'), -- Count distinct login days per user
FILTER(
'DateFilteredDailyUsageReport',
'DateFilteredDailyUsageReport'[FormattedDate] >= [StartDateSelected] &&
'DateFilteredDailyUsageReport'[FormattedDate] <= [EndDateSelected] && -- Respect the selected date range
WEEKDAY('DateFilteredDailyUsageReport'[FormattedDate], 2) < 6 -- Only weekdays (Mon-Fri)
)
)

3. Now I would like to achieve how many users are there with same logincount. So I want my 4th step output like first column should be LoginCount second column should be how many users are logged in for example below
LoginCount Number of Users
19 5
20 4

[Kedar_Pande]

@chetanpatel14 

Create a calculated table

LoginCountSummary =
SUMMARIZE(
ADDCOLUMNS(
VALUES('DateFilteredDailyUsageReport'[Name]),
"LoginCount", 
CALCULATE(
COUNTROWS('DateFilteredDailyUsageReport'),
FILTER(
'DateFilteredDailyUsageReport',
'DateFilteredDailyUsageReport'[FormattedDate] >= [StartDateSelected] &&
'DateFilteredDailyUsageReport'[FormattedDate] <= [EndDateSelected] &&
WEEKDAY('DateFilteredDailyUsageReport'[FormattedDate], 2) < 6
)
)
),
[LoginCount],
"Number of Users", COUNTROWS(VALUES('DateFilteredDailyUsageReport'[Name]))
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[grazitti_sapna]

Hi @chetanpatel14 ,
To achieve your desired output, you need to group the users by their LoginCount and count how many users fall into each group. Here’s how you can achieve this in Power BI:

1. Create a Calculated Table
   Use the LoginCountPerUserMeasure to calculate the LoginCount for each user and then create a summary table.

2. Write a DAX Table Calculation
   Use the following DAX formula to create a new calculated table that groups the data and counts the number of users for each LoginCount:     
   LoginCountSummary =
   SUMMARIZE(
   ADDCOLUMNS(
   DISTINCT('DateFilteredDailyUsageReport'[Name]),
   "LoginCount", [LoginCountPerUserMeasure]
   ),
   [LoginCount],
   "Number of Users", COUNTROWS(VALUES('DateFilteredDailyUsageReport'[Name]))
   )

3. Use the Table in a Visual
   Add the new table LoginCountSummary to a table visual to display the results.

Output
The table visual should display:

Login Count	Number Of Users
19	5
20	4

This will dynamically reflect changes in the slicer selection. Let me know if you need further assistance!
If I have resolved your question, please consider marking my post as a solution. Thank you!

[chetanpatel14]

Above provided DAX is not working, I already validated. This will not respect the date range which user selected, it calculates the output for entire dataset from the sourcetable. It will not calculate based on the intermediate table which I created based on the date slicer

[SamWiseOwl]

Hi @chetanpatel14 

Your question is very similar to this one: 
https://community.fabric.microsoft.com/t5/Desktop/Need-to-change-the-category-and-counts/m-p/4287005...

You need to create a data table that contains the number of Logins you want to test for.

If you just want numbers you could use LoginCount = GenerateSeries(1,100,1) that will generate a list of numbers 1 to 100.

Then you can use this in your table visual along with a measure:

Count Logins=

var logintable = --Create a virtual version of your table, calc for each person their login no

SUMMARIZE('logintable ','logintable'[Name], "Count login", [LoginCountPerUserMeasure] )

var currNum = SelectedValue(LoginCount[Value]) --capture the current rows login count

var final =

COUNTROWS(FILTER(logintable, [Count login] = currNum)) --Filter the login to only people with that no of logins

RETURN final

[chetanpatel14]

Hi,

I didn't get this one
var logintable = --Create a virtual version of your table, calc for each person their login no. How to create this. Can you please provide the steps to achieve the desired output.

Thanks again for your response.

[SamWiseOwl]

If you mean this bit:

var logintable = --Create a virtual version of your table, calc for each person their login no

SUMMARIZE('logintable ','logintable'[Name], "Count login", [LoginCountPerUserMeasure] )

The -- is a comment letting you know what the SUMMARIZE function is doing.

So it is going to your table 'public dailyusagereport' and for each name performing the measure you made. Replace LoginTable with the name of your table.

[chetanpatel14]

Hi,

Thanks for your quick response, I already created the table with the series, What I asked is I didn't understood the below code.

Count Logins=

var logintable = --Create a virtual version of your table, calc for each person their login no

SUMMARIZE('logintable ','logintable'[Name], "Count login", [LoginCountPerUserMeasure] )

var currNum = SelectedValue(LoginCount[Value]) --capture the current rows login count

var final =

COUNTROWS(FILTER(logintable, [Count login] = currNum)) --Filter the login to only people with that no of logins

RETURN final

Thanks

[SamWiseOwl · Accepted solution]

It is creating a measure that will use your existing measure on your table 'public dailyusagereport' to work out how many logins each person has and then group them.

Replace LoginTable with the name of your table like so:

Count Logins=

var logintable = --Create a virtual version of your table, calc for each person their login no

SUMMARIZE('public dailyusagereport','public dailyusagereport'[Name], "Count login", [LoginCountPerUserMeasure] )

var currNum = SelectedValue(LoginCount[Value]) --capture the current rows login count

var final =

COUNTROWS(FILTER(logintable, [Count login] = currNum)) --Filter the login to only people with that no of logins

RETURN final

The -- are just comments to explain what the code is doing. You can delete these 🙂

[chetanpatel14]

Hi, Thanks for your solution it worked. I did below steps
1. Created new table 

LoginCount = GenerateSeries(1,100,1)

2. Created new measure with below DAX

UserCountByLoginCount =

VAR logintable =

    SUMMARIZE(

        'DateFilteredDailyUsageReport',

        'DateFilteredDailyUsageReport'[Name],

        "CountLogin", [LoginCountPerUserMeasure]

    )

VAR currNum = SelectedValue(LoginCount[Value])    

VAR result =

    COUNTROWS(FILTER(logintable, [Countlogin] = currNum))

RETURN result

3. In the clustered column chart, dragged the value field from the LoginCount table and added UserCountByLoginCount from the DateFilteredDailyUsageReport table. It is displaying the output as expected

Thanks again for your response.