DAX: Calculating the number of rows with a condition

[ExcelBot0815]

Hello everyone,

I am pretty new to PowerBI and have a question.

I want to do something rather simple but I am lacking the syntax to do it. I constantly produce errors or wrong results, which is why I am trying my luck on here.

A simplified version of what I want to do is this:

I have column A and B in my data set and I want to calculate column C and D in the data model:

• In column C I want to count the number of rows that a certain product (column A) has within the given category (column B)
• Then in column D I want to rank the products by number of rows per category.

I know that I have to use "calculate" and "countrows" and "filter" and "rank" but I just can't figure out how to solve this. Please note that I can't use absolute values for neither product nor category, because I have 539 distinct products in 259 distinct categories.

Any input is very much appreciated.

[Greg_Deckler · Accepted solution]

Try out these two columns:

Count = 
VAR __table = FILTER(ALL('Table7'),[A] = EARLIER([A]) && [B] = EARLIER([B]))
RETURN
COUNTX(__table,[B])

Rank = RANKX(FILTER(ALL('Table7'),[B] = EARLIER([B])),[Count],,DESC,Dense)

See Table7 of attached.

[ExcelBot0815]

Works as intended. Thank you very much.