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

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft 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
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

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

      Regards,

      Pat

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        That did not help.. I have PBI attached with sample data in my OP, if you want to take a look