Solved: Duplicate counts issue - Page 2

Anonymous · ‎06-26-2020

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.

Sample Data.pbix

mahoneypat · ‎07-02-2020

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

View solution in original post