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
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
Microsoft EmployeeNow 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
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- mahoneypat
Glad you got it worked out. Please mark as solution. Kudos appreciated too.
Regards,
Pat
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 () ) ) )