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

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

     

13 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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] ) )

       




      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        In the @maxcat virtual column, try changing ALL() to ALLSELECTED().  Please let me know if that works.

        Regards,

        Pat