Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Duplicate counts issue

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...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

IDDay No
11-Day
22-Day
33-Day
44-Day
55-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] )

 

 

 
 
 

 

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

    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!!