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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
shanebo3239
Helper I
Helper I

DAX RANKX, Filter, and ALLEXCEPT Question

All,

 

I have two tables in question.  Table A is the Users table.  Table B is the transactions table.  They are related by a field called userid.  They look like this:

 

Table A:

UserId   |  UserName 

1 | John

2 | Bob

3 | Bill

 

Table B:

TransactionID  |  UserID  | TransactionType

1234 | 1 | 50

1235 | 3 | 50

1237| 1 | 50

1231 | 2 | 48

1239 |1 | 50

1242 | 3 | 50

1268 | 3 | 50

 

So, as you can see, user 1 (John) has performed 3 transactions, as has user 3 (Bill).  All of their transactions are a "type 50", which is translated in another table to the true name of the transaction type.  What I need to do is to rank them by the number of transactions they've done, but filter out anyone with 2 or less transactions.  On a large scale, I want to do rankings on employees transactions but throw out anyone with a small number from the dataset. 

 

The end result should allow me to select a user name with a slicer, then have it give me their rank for a particular transaction type.  So a slicer for username and a visual level filter for transaction type.  This would give their ranking in that particular transaction type.

 

I've played around with RankX, Filter, and ALLEXCEPT but no joy.

 

It's more complex than what I'm telling here, but if someone could point me in the right direction, I'd appreciate it.

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

Create a Transactions Measure

Transactions = COUNTROWS ('Table B')

Then your Rank Measure

Rank Users =
IF (
    HASONEVALUE ( 'Table A'[UserName] ),
    RANKX ( ALLSELECTED ( 'Table A'[UserName] ), [Transactions],, DESC, DENSE ),
    BLANK ()
)

Then use User Name and Rank Users in a Table Visual

drag the Transactions Measure to the Visual Level Filters ans select is greater than - type 2

 

Ranking Measure.png

View solution in original post

1 REPLY 1
Sean
Community Champion
Community Champion

Create a Transactions Measure

Transactions = COUNTROWS ('Table B')

Then your Rank Measure

Rank Users =
IF (
    HASONEVALUE ( 'Table A'[UserName] ),
    RANKX ( ALLSELECTED ( 'Table A'[UserName] ), [Transactions],, DESC, DENSE ),
    BLANK ()
)

Then use User Name and Rank Users in a Table Visual

drag the Transactions Measure to the Visual Level Filters ans select is greater than - type 2

 

Ranking Measure.png

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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