0

First time posting, but I'm at my wits end.

I have a workbook with about 8,000 rows, but it's bogged down by several fields containing nested, conditional statements. (sumifs,averageifs,countifs) So, trying to migrate to PowerPivot, but have no experience working with PowerPivot. Found similar SUMIFS conversions at

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b50f7b74-d459-4033-954e-53158f900334/excel-sumifs-and-the-powerpivot-version-help-please?forum=sqlkjpowerpivotforexcel

and

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/49e77cb7-b7c3-4eb1-85ed-963e5cce8e64/vlookup-and-countifs-in-powerpivot?forum=sqlkjpowerpivotforexcel

but the nesting functions just seem to be tripping me up. Spent the first half of the day on it and, so far, this is the best I've got. Please save me from myself. Thanks!

IFERROR(
IF([Savings_TYPE]="[E]",

IF(OR([Savings_DATE]<NOW()-30,
      Calculate(Sum([Realized Savings]),ALL(PMO_TEST_View_1,[Project_ID_FK],[Savings_DATE]>0)),
      Calculate(Sum([Realized Savings]),ALL(PMO_TEST_View_1,[Project_ID_FK],[Savings_DATE]))),

IF(OR([Current_Status]="complete",
      [Current_Status]="Cancelled",
      [Current_Status]="on hold",
      [Current_Status]="delete"),0,

IF(AND(
   IFERROR(
   Calculate(average([Realized Savings]),[Project_ID_FK],[Savings_DATE]>=NOW()-100,[Savings_DATE]<NOW())=0,0),
   Calculate(COUNTROWS(([Discounted Savings]),Filter(PMO_TEST_View_1,[Discounted Savings]=EARLIER([Project_ID_FK]&&[Savings_DATE]>=NOW()-100,[Savings_DATE]<NOW())>1),0,
   MIN([Discounted Savings],
   IFERROR(Calculate(average([Realized Savings],ALL([Project_ID_FK],[Savings_DATE]>=NOW()-100,[Savings_DATE]<NOW(),[Realized Savings]<>0)),1000000000))))),0),0)

The following already works in Excel.

=IFERROR(
   IF([@[Savings_TYPE]]="[E]",
   IF(OR([@[Savings_DATE]]<NOW()-30,
         SUMIFS([Realized Savings],[Project_ID_FK],[@[Project_ID_FK]],[Savings_DATE],[@[Savings_DATE]])>0),
         SUMIFS([Realized Savings],[Project_ID_FK],[@[Project_ID_FK]],[Savings_DATE],[@[Savings_DATE]]),

   IF(OR([@[Current_Status]]="complete",
         [@[Current_Status]]="Cancelled",
         [@[Current_Status]]="on hold",
         [@[Current_Status]]="delete"),0,

   IF(AND(
      IFERROR(AVERAGEIFS([Realized Savings],[Project_ID_FK],[@[Project_ID_FK]],[Savings_DATE],">="&NOW()-100,[Savings_DATE],"<"&NOW())=0,0),
      COUNTIFS([Discounted Savings],">0",[Project_ID_FK],[@[Project_ID_FK]],[Savings_DATE],">="&NOW()-100,[Savings_DATE],"<"&NOW())>1),
      0,MIN([@[Discounted Savings]],
      IFERROR(AVERAGEIFS([Realized Savings],[Project_ID_FK],[@[Project_ID_FK]],[Savings_DATE],">="&NOW()-100,[Savings_DATE],"<"&NOW(),[Realized Savings],"<>0"),1000000000))))),0),0)
Evan
  • 1
  • 1
  • I think you should try to execute this in multiple measures, and see which code is working. Writing this in 1 dax measure from scratch will not work. Anyhow you code is not working because the "OR-function" only allows 2 values. syntax: OR(,) Replace this with (||||) – WimV May 25 '16 at 09:58
  • I've been testing each component separately, but — I have to admit — I assumed something as simple as "OR" would be the same. Here I've been blaming the CALCULATE string! Thanks! – Evan May 25 '16 at 12:05

0 Answers0