Forum Discussion
Duplicate counts issue
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
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
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!!