Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to get unique counts over selected period. A user could have multiple categories (1 per day) and in that case, i would like the user to be associated to only one category.
For example in the below image, User "SL-AE01" and "SL-08" have 2 distinct categories and is counted twice in the total. In such case, i would like that to be counted towards category "2" total only.
Image 2 shows the expected output
Attached is a power bi file with sample data.
Solved! Go to Solution.
Now that I have a better understanding of your goal, this is the measure I probably would have written first. It seems to get the correct results in your sample pbix. I added comments to explain how it works.
Count in Category =
VAR __thiscategory =
MAX ( Test[CategoryId] ) //Store the CategoryId in context of the visual as a variable. MAX to avoid result of 1 in Totals.
VAR __summary =
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( Test[User] ), //get list of users in the current context
"@NotFacility", CALCULATE ( //count how many days this user was not at a Facility in the current context
COUNTROWS ( Test ),
ALL ( Test[CategoryId] ), //removes teh filter from the CategoryId
ALL ( WorkCategory ), //removes the filter from Category Name
Test[CategoryId] <> 1
) + 0
),
Dates[Day Name] <> "Sat" //Make the table above excluding Saturdays
)
RETURN
IF (
__thiscategory = 1, //do different calculation based on if Facility or not facility in the visual
COUNTROWS ( FILTER ( __summary, [@NotFacility] = 0 ) ), //exclude rows where user worked somewhere other than a facility too
COUNTROWS ( __summary ) //count all rows for non-facility categories
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here is one way to do it:
NewMeasure =
VAR __summarytable =
ADDCOLUMNS (
SUMMARIZE ( Test, Test[User], Test[CategoryId] ),
"@maxcat", CALCULATE ( MAX ( Test[CategoryId] ), ALL ( Test[CategoryId] ) )
)
RETURN
COUNTROWS ( FILTER ( __summarytable, Test[CategoryId] = [@maxcat] ) )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks @mahoneypat It works but when i use the category name as columns, in other table with relation 1 --> * (test) , it shows incorrect data (img 2 from my OP)
Also, i have to apply filters before summarizing the data. to exclude weekend data (from the dates table) and other category id's (3,4) from these counts.
So, i changed the query slightly but it doesn't seem to work, could you pls take a look and help.
Thanks
NewMeasure 1 =
VAR CombinedTable = CALCULATETABLE( Test, FILTER(Dates, Dates[Day Name] <> "Sat" ))
VAR __summarytable =
ADDCOLUMNS (
SUMMARIZE ( CombinedTable, Test[User], Test[CategoryId] ),
"@maxcat", CALCULATE ( MAX ( Test[CategoryId] ), ALL ( Test[CategoryId] ) )
)
RETURN
COUNTROWS ( FILTER ( __summarytable, Test[CategoryId] = [@maxcat] ) )
In the @maxcat virtual column, try changing ALL() to ALLSELECTED(). Please let me know if that works.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
That did not help.. I have PBI attached with sample data in my OP, if you want to take a look
Please try this expression to allow you to use the Category Name column in the matrix.
NewMeasure =
VAR CombinedTable =
CALCULATETABLE ( Test, FILTER ( Dates, Dates[Day Name] <> "Sat" ) )
VAR __summarytable =
ADDCOLUMNS (
SUMMARIZE ( CombinedTable, Test[User], WorkCategory[Id] ),
"@maxcat", CALCULATE (
MAX ( Test[CategoryId] ),
ALL ( Test[CategoryId] ),
ALL ( WorkCategory )
)
)
RETURN
COUNTROWS ( FILTER ( __summarytable, WorkCategory[Id] = [@maxcat] ) )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat - i just noticed while verifying the counts that in case if there's a user with category id - 2 & 4 then it is excluding those users from the count, because the Filter in the variable table is filtering on Category Id's - (1,2), while the max returns 4
User | CategoryId | MaxCategory |
A | 1 | 4 |
A | 2 | 4 |
B | 2 | 4 |
I've figured out the solution to count users by no Of days. While verifying the counts from both measures, i came across this issue. Pls help
Now that I have a better understanding of your goal, this is the measure I probably would have written first. It seems to get the correct results in your sample pbix. I added comments to explain how it works.
Count in Category =
VAR __thiscategory =
MAX ( Test[CategoryId] ) //Store the CategoryId in context of the visual as a variable. MAX to avoid result of 1 in Totals.
VAR __summary =
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( Test[User] ), //get list of users in the current context
"@NotFacility", CALCULATE ( //count how many days this user was not at a Facility in the current context
COUNTROWS ( Test ),
ALL ( Test[CategoryId] ), //removes teh filter from the CategoryId
ALL ( WorkCategory ), //removes the filter from Category Name
Test[CategoryId] <> 1
) + 0
),
Dates[Day Name] <> "Sat" //Make the table above excluding Saturdays
)
RETURN
IF (
__thiscategory = 1, //do different calculation based on if Facility or not facility in the visual
COUNTROWS ( FILTER ( __summary, [@NotFacility] = 0 ) ), //exclude rows where user worked somewhere other than a facility too
COUNTROWS ( __summary ) //count all rows for non-facility categories
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat - Thanks! It does solve the issue with the counts but i am unable to apply additional filters. I did not include the entire filter previously used in the measure. I've added additional tables and data (used in the below measure) to pbix file.
I made the following changes to the measure, you shared originally. Date filter is applied differently for each country due to their work day/schedule
NewMeasure 1 =
VAR CombinedTable = CALCULATETABLE( Test,
FILTER
(
Test,
Test[CategoryId] IN {1,2}
&&
IF (
LEFT ( Test[County ID], 1) = RELATED('Country - WorkDays'[CountryCode]),
RELATED(Dates[IsWeekend MEA]) = FALSE (),
RELATED(Dates[IsWeekend ROW]) = FALSE ()
)
)
)
VAR __summarytable =
ADDCOLUMNS (
SUMMARIZE ( CombinedTable, Test[User], Test[CategoryId] ),
"@maxcat", CALCULATE ( MAX ( Test[CategoryId] ), ALL ( Test[CategoryId] ), ALL(WorkCategory) )
)
RETURN
COUNTROWS ( FILTER ( __summarytable, Test[CategoryId] = [@maxcat] ) )
Appreciate your help and thank you much for taking time to add the comments, very helpful to understand
Tele Sample data v1.pbix
Glad you got it worked out. Please mark as solution. Kudos appreciated too.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I missed your response. Sorry, if i wasn't clear but i do not have the complete solution yet. The recent method you shared solves the issue with the counts but i am unable to use the filters (like below) in the calculate table expression. Could you pls help
I need to use a filter, similar to this, to exclude weekends for countries based on their working days
VAR CombinedTable = CALCULATETABLE( Test, FILTER ( Test, Test[CategoryId] IN {1,2} && IF ( LEFT ( Test[County ID], 1) = RELATED('Country - WorkDays'[CountryCode]), RELATED(Dates[IsWeekend MEA]) = FALSE (), RELATED(Dates[IsWeekend ROW]) = FALSE () ) ) )
Thanks @mahoneypat . That works perfectly
I've another related question, i would like to get the count of users grouped by No of Days At Facility. In general, the data is grouped by a week or month (below chart).
I modified the query ("Facility Day Count") you shared and it works perfectly, table shows the data correcty but i need to use this in a graph. So, i created a disconnected table with following data and used the below measures, "Facility Day Count" - to count the days and "At Facility - Days" for lookup and group them but this isn't giving me expected output
ID | Day No |
1 | 1-Day |
2 | 2-Day |
3 | 3-Day |
4 | 4-Day |
5 | 5-Day |
Facility Day Count =
VAR CombinedTable = CALCULATETABLE( Test, FILTER(Dates, Dates[Day Name] <> "Sat" ))
VAR __summarytable =
ADDCOLUMNS (
SUMMARIZE ( CombinedTable, Test[User], Test[CategoryId] ),
"@maxcat", CALCULATE ( COUNT ( Test[CategoryId] ) )
)
RETURN
MAXX(__summarytable, [@maxcat])
At Facility - Days =
VAR UserAtFacilityCounts =
SUMMARIZE (
'Test',
'Test'[User],
"DayCount", CALCULATE (
IF ( [Facility Day Count]
= MAX ( 'Day Number'[ID] ),
1, 0
)
)
)
RETURN
sumx( UserAtFacilityCounts, [DayCount] )
This second question could have been another post. In any case, are you sure your Facility Day Count measure is correct? It looks like will return the max # of days whether someone works at the Facility or at Home. If you are looking for a measure to count the number of people that worked a given # of days each period (week), the measure can probably be simplified. Can you explain why if someone works at Home and Facility in a given week, you want the max category ID (in which case Home is higher than Facility)?
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Facility Day Count =
VAR CombinedTable = CALCULATETABLE( Test, FILTER(Dates, Dates[Day Name] <> "Sat" ), FILTER(Test, 'Test'[CategoryId] = 2))
VAR __summarytable =
ADDCOLUMNS (
SUMMARIZE ( CombinedTable, Test[User], Test[CategoryId] ),
"@maxcat", CALCULATE ( COUNT ( Test[CategoryId] ) )
)
RETURN
MAXX(__summarytable, [@maxcat])
Sorry, it was missing additional filter. I am only interested in user counts at facility. Due to these recent scenarios, user has the option to go to the facility or wfh but for a given day with other criteria, we choose either 1, there are also other categories
I am a newbie and hope my understanding of the measure is correct - Var CombinedTable -> Filters the table, Var Summary -> get the counts per user, from filtered table. Finally Maxx returns the maxx count value for each user. In this case because we should only have 1 count per user, using any aggregate - MINX, MAXX should return the same right? Please correct me, if i am wrong here
So, if User 'A' & User 'B' work at the facility 3 days, User 'C' - 1 day and User 'D' - 4 days (in the same week) then we would like to show the counts as '1-Day' -> 1 , '2-Day' -> 0, '3-Day' -> 2, '4-Day' -> 1, '5-Day' -> 0
Thanks for your help!!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |