Compare counts with in group, rank and count the top values

[Anonymous]

@mahoneypat - i need your help, requirement has slightly changed to the solution you shared previously (URL below). I've been struggling to get the output correctly. 
Pls help modify the DAX you shared, to compare the counts by category id and pick the highest and choose category id - 2, in case of a tie

https://community.powerbi.com/t5/Desktop/Duplicate-counts-issue/m-p/1198974#M536475 

[v-alq-msft]

Hi, @Anonymous 

Could you please show us the expected result which may help understand and solve the problem?

Best Regards

Allan

[Anonymous]

I'll try to explain the scenario - There are multiple users assigned to a county. A user can work at location or home on a given day. I would like to compare the numbers of days, a user worked at the location to the days at home (for ex: in a week or month) and based on the max count, assign him to the category - location or home for the given period and count the number of users in each category for selected period for the county.  If the user works equal number of days at location and home in the period (week/month), location category will take priority ( for Ex: User A - week 08/09)

Also, I would prefer using a measure rather than calculated tables, considering size of the data and the requirements

Thank You

Raw data Table
County	User	Date	Category Id
1	A	8/3/2020	1
1	A	8/4/2020	2
1	A	8/5/2020	1
1	A	8/6/2020	2
1	A	8/7/2020	1
1	B	8/3/2020	1
1	B	8/4/2020	2
1	B	8/5/2020	2
1	B	8/6/2020	2
1	B	8/7/2020	2
1	C	8/3/2020	1
1	C	8/4/2020	2
1	C	8/5/2020	1
1	A	8/10/2020	1
1	A	8/11/2020	1
1	A	8/12/2020	2
1	A	8/13/2020	2
1	B	8/10/2020	2
1	B	8/11/2020	2
1	B	8/12/2020	2
1	B	8/13/2020	1

Category Table
Category Id	Category
1	Home
2	Location
4	Other

Calendar Table
Year	Month	Week Start	Date
2020	Aug	8/2/2020	8/3/2020
2020	Aug	8/2/2020	8/4/2020
2020	Aug	8/2/2020	8/5/2020
2020	Aug	8/2/2020	8/6/2020
2020	Aug	8/2/2020	8/7/2020
2020	Aug	8/2/2020	8/8/2020
2020	Aug	8/9/2020	8/9/2020
2020	Aug	8/9/2020	8/10/2020
2020	Aug	8/9/2020	8/11/2020
2020	Aug	8/9/2020	8/12/2020
2020	Aug	8/9/2020	8/13/2020

[Greg_Deckler]

@Anonymous - If I am understanding this correctly, I would actually leave the category table as a disconnected table, no relationships. The reason is because you wish to use a measure and will become clear in a minute. To assign a value for an individual the basic pattern should be something along the lines of:

Measure =

  VAR __User = MAX('Raw'[User])

  VAR __Table = SUMMARIZE(ALL('Raw'),[User],[Category Id],"Count",COUNTROWS('Raw'))

  VAR __Max = MAXX(FILTER(__Table,[User] = __User),[Count])

  VAR __Category = MAXX(FILTER(__Table,[User] = __User && [Count] = __Max),[Category Id])

RETURN

  LOOKUPVALUE('Category'[Category],'Category'[Category Id], __Category)

Something along these lines should return your category for each user if you put User and this measure in a table visualization.

Now, in order to use this in your column chart visualization you will want to use the disconnected table trick. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563

In your case, it would probably be something like this:

Measure Count =

  VAR __Category = MAX('Category'[Category])

  VAR __Table = ADDCOLUMNS(SUMMARIZE('Raw',[User]),"Measure',[Measure])

RETURN

  COUNTROWS(FILTER(__Table,[Measure] = __Category))

This second part is basically a measures aggregation issue. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

[Anonymous]

@Greg_Deckler 

I've tested the DAX using test data i shared and it works perfectly, still need to test on my data set. However, when i have a user with equal number of rows with location and home, location (Category id = 2) should take precedence. Ex: User 'A' in week 8/10, should be counted towards location

Also, DAX for measure counts seems to work with related table as well. Am i missing something? I need to have a relation between them as there are several other visuals dependent on it, that show different views

Thank you!