0

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)

mal-wan
  • 4,391
  • 4
  • 26
  • 37

1 Answers1

1

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.

Stewart R
  • 48
  • 7
  • Thanks Stewart, I should add that for us this data is all in different tables. We have a Community table, a Disbursements/Payments table and a Student table. Is there a way to use Filter in this fashion over multiple tables of info? – mal-wan Aug 04 '16 at 21:56
  • I've added an update for the related tables case - if it's still not quite enough, it might be a good idea to update your question with some more details of the PowerPivot model behind. Hope it helps, though! – Stewart R Aug 05 '16 at 07:20
  • That's helped tremendously! Thanks! I'll put some time into updating my question with my final solution for the sake of others stumbling upon this. – mal-wan Aug 10 '16 at 23:12