Solved

How to filter using DAX in PowerPivot 2013

Posted on 2013-11-04
4
624 Views
Last Modified: 2013-11-05
I'm working with PowerPivot in Excel 2013. Data is loaded from SQL Server 2012.

I have a data table named Results (ResultID int, OrderOpenTime datetime, AccountBalance decimal, AccountEquity decimal, DateKey date) and a lookup table DimDates with various time dimensions. The Results table is updated about every 5 minutes with the latest account and equity number which is good for trend pivots, but I have a problem with grouping to show results.

I've added measures to the Results table called FirstRowDateTime and LastRowDateTime that correctly gives me the first and last row for the selected time period, but I'm unable to use them for filtering.

By my limited understanding
:=CALCULATE(SUM([AccountBalance]);FILTER(Results; Results[OrderOpenTime]=[FirstRowDateTime]))
should calculate the sum of only one row (the first one in the chosen time period of the pivot table), but instead I get the sum of all [AccountBalance] as if the FILTER statement wasn't even there.

What am I missing? I've included a spreadsheet with sample data.

TIA!
Dennis
DAX-problem.xlsx
0
Comment
Question by:Dennis_Gundersen
  • 2
  • 2
4 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 39624508
I think all you need is:

=SUMX(TOPN(1,Results, Results[OrderOpenTime],1),[AccountBalance])
0
 

Author Comment

by:Dennis_Gundersen
ID: 39624603
Thank you, that's very close. When I replace Results[OrderOpenTime] with my measure for the same thing I get the formula
=SUMX(TOPN(1;Results;[FirstRowDateTime];1);[AccountBalance])
which does give me the incoming balance, but I'm not quite sure the formula works the right way since when I replace "first row" with "last row" as in
=SUMX(TOPN(1;Results;[LastRowDateTime];1);[AccountBalance])
I get the same result.

The first row should return $2000.00, but the second formula should return $2018.40.

By changing the ordering direction to 0 for the second formula, I fortunately get the value I need, so I can finally move on using this solution, but it still looks to me like the First/LastRowDateTime measure is more or less ignored as a filter, which I fail to understand.

Re
D

Update: Never mind, I get it now. SUMX is an iterator so it looks through all the rows. I probably shouldn't use an iterator on a data table, but I'll take it. Thanks!
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39624715
You don't need your First/Last measures at all. For the FirstBalance, you use:

=SUMX(TOPN(1;Results;Results[OrderOpenTime];1);[AccountBalance])
and for the last balance you use:
=SUMX(TOPN(1;Results;Results[OrderOpenTime];0);[AccountBalance])
0
 

Author Comment

by:Dennis_Gundersen
ID: 39624729
Yes, I just posted an update to my reply. Took me awhile to remember the difference between SUM and SUMX. Thank you very much, I've been stuck on this problem for 2 weeks.

Re
Dennis
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now