Dennis_Gundersen
asked on
How to filter using DAX in PowerPivot 2013
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([AccountBa lance]);FI LTER(Resul ts; Results[OrderOpenTime]=[Fi rstRowDate Time]))
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
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([AccountBa
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You don't need your First/Last measures at all. For the FirstBalance, you use:
=SUMX(TOPN(1;Results;Resul ts[OrderOp enTime];1);[AccountBalance])
and for the last balance you use:
=SUMX(TOPN(1;Results;Resul ts[OrderOp enTime];0);[AccountBalance])
=SUMX(TOPN(1;Results;Resul
and for the last balance you use:
=SUMX(TOPN(1;Results;Resul
ASKER
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
Re
Dennis
ASKER
=SUMX(TOPN(1;Results;[Firs
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;[Last
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!