Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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. 

vdr_0-1593209422767.png

 

vdr_1-1593210063777.png

Sample Data.pbix 

1 ACCEPTED SOLUTION

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

13 REPLIES 13
mahoneypat
Employee
Employee

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





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


Anonymous
Not applicable

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

vdr_0-1593473570234.png

 




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

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


Anonymous
Not applicable

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

Please try this expression to allow you to use the Category Name column in the matrix.

 

NewMeasure =
VAR CombinedTable =
    CALCULATETABLE ( Test, FILTER ( Dates, Dates[Day Name] <> "Sat" ) )
VAR __summarytable =
    ADDCOLUMNS (
        SUMMARIZE ( CombinedTable, Test[User], WorkCategory[Id] ),
        "@maxcat", CALCULATE (
            MAX ( Test[CategoryId] ),
            ALL ( Test[CategoryId] ),
            ALL ( WorkCategory )
        )
    )
RETURN
    COUNTROWS ( FILTER ( __summarytable, WorkCategory[Id] = [@maxcat] ) )

 

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


Anonymous
Not applicable

@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 

UserCategoryIdMaxCategory
A14
A24
B24

 

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

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


Anonymous
Not applicable

@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 

Glad you got it worked out.  Please mark as solution.  Kudos appreciated too.

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


Anonymous
Not applicable

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


 

Anonymous
Not applicable

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

 

 

 
 
 

 

2020-06-30_1-16-28.png

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

 





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


Anonymous
Not applicable

 

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

 
 
 

Capture.JPG

 

 

Thanks for your help!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.