cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ExcelBot0815 Frequent Visitor
Frequent Visitor

DAX: Calculating the number of rows with a condition

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:

Unbenannt.PNG

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: DAX: Calculating the number of rows with a condition

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


2 REPLIES 2
Highlighted
Super User
Super User

Re: DAX: Calculating the number of rows with a condition

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


ExcelBot0815 Frequent Visitor
Frequent Visitor

Re: DAX: Calculating the number of rows with a condition

Works as intended. Thank you very much.