Count and rank combinations of values in two columns

[twilliams98103]

I need to find the top n count of combinations across two columns. As a simplified example, given the following dataset:

COLOR	STYLE
Red	A
Red	A
Red	A
Red	A
Red	B
Green	A
Green	A
Green	B
Green	B
Green	B
Blue	A
Blue	C

I would need to determine that Red-A is the top combination with 4 occurences, Green-B second with 3, and Green-C third with 2, and display those three values (4,3,2) with meaningful lables. My actual dataset has millions of rows and thousands of unique combinations across the two columns. I have played around with the technique illustrated in this post but am not quite there. I have also reviewed some similar solutions using PowerQuery Group By but my connection type is DirectQuery, so I'm not sure if they will work.

For display I experimented with a matrix because I thought it would simplify the labelling, but the client would prefer a multi-line card, which I assume would require a concatenated label.

Thanks in advance for any help you can provide.

[az38 · Accepted solution]

@twilliams98103 

or even this

Table = 
var _tbl = SUMMARIZE(Table1;[STYLE];[COLOR];"Count";COUNTROWS(Table1))
return
ADDCOLUMNS(_tbl;"Rank";rankx(_tbl;[Count];;DESC)
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[tanmayvs]

Hello, 

This solution helped me get what I was desiring. However, I would like to use a date slicer in my dashboard, and the output of this table does not dynamically change with this slicer. It does not allow me to create a relationship between this Table and my original table, Table1. The code is similar to what you showed above, but I'm unable to find away to embed the corresponding date/time value so that I can filter and then summarize the data.

Thanks!

[az38]

Hi @twilliams98103 

try to create a calcualted table

Table = 
SUMMARIZE(Table1;[STYLE];[COLOR];"Count";COUNTROWS(Table1))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[amitchandak]

Refer if this can help

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures