CODEWITHSUNDEEP
excelpowerpivotpowerbidaxssas-tabular
mal-wan
mal-wan

Reputation: 4476

DAX Rank by Date

I am Counting on Distinct ID's in a column - this is leading to the sum of the subtotals not equalling the grand total as follows:

enter image description here

What I want to do is rank the Payment Dates in cronological order and select ONLY the highest date to display. In the example above the Grand Total won't change, but the Townville row will not show a Distinct Student Count.

This is a very specific requirement and I'm assuming there's an easy way to do it in DAX - I've tried playing around with both RANKX and MAX but am no closer to solving this.

One last thing - the Rank must be contextual to the Time Filter selected by the user (so if they select 2015 it'd give the second record Rank 1 and the top record wouldn't show. If they select May 2015 it'd give the top record Rank 1 and the second record wouldn't show)

Upvotes: 0

Views: 8985

Answers (1)

Stewart R
Stewart R

Reputation: 48

I think this is what you are looking for - I added a calculated column to the PowerPivot model that provides a rank based on the Last Payment Date and the Name of the Student. It will rank the earliest payment for any student as a 1.

The code for the column is as follows:

=RANKX(FILTER(Table1, [Student Name] = EARLIER([Student Name])), [Last Payment Date])

... assuming your table is named "Table1"!

The FILTER is the key that limits the ranking to dates belonging to students with that name only.

Update for Multiple tables

To set up relationships between the tables, go to the "Diagram View" of the model, available in the Home tab of the Power Pivot window.

You can drag fields from one table to the other to create relationships. This will only work if at least one of the fields is unique - it's a good idea to think of the model as a dimensional model, with a tables that acts like a fact and other tables around it that act like dimensions.

From the comment, I would try to get the Payments to act like the fact, and have it link to the Community and Student tables. in this case, you could then have the following code:

=RANKX(FILTER(Table1, Related('Students'[Student Name]) = EARLIER('Students'[Student Name])), [Last Payment Date])

This calculated column would be on your Payments Fact table, and it uses a lookup to a related field.

Note that in this specific case, it would be easier to just run the filter over your Student ID field that is used to lookup the Student name.

Upvotes: 1

Related Questions