Forum Discussion
Summarize the Measure output
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
- 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 noSUMMARIZE('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 loginsRETURN finalThe -- are just comments to explain what the code is doing. You can delete these 🙂
9 Replies
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#M1346153You 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 noSUMMARIZE('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 loginsRETURN finalHi,
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.
If you mean this bit:
var logintable = --Create a virtual version of your table, calc for each person their login noSUMMARIZE('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.
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:- Create a Calculated Table
Use the LoginCountPerUserMeasure to calculate the LoginCount for each user and then create a summary table. 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]))
)- 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!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
- Create a Calculated Table
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
