Forum Discussion

Jawroy's avatar
Jawroy
Regular Visitor
9 years ago
Solved

powerpivot OR function limited

I get the message "The end of the input was reached". What I'm trying to do is have multiple 'OR'-functions in my table. I've tried || , but that didn't help much.   What I need is; column [dept] ...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Jawroy,

    I try to reproduce your scenario and get expected result as follows.

    1. Type your data into Excel table, click PowerPivot->Add to Date Model under home page.

     



    2. Create a rank calculated column using the formula.

    =RANKX(Table1,Table1[dept],,ASC)


    3. Based on rank column value, calculate the expected result using formula.

    =IF(AND(Table1[Rank]>=1,Table1[Rank]<=3),"X",IF(Table1[Rank]<=7,"Y","Z"))

     
    Please see the result shown in following screenshot.



    If you have other issues, don't hesitate to let me know.

    Best Regards,
    Angelia



v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi Jawroy,

I try to reproduce your scenario and get expected result as follows.

1. Type your data into Excel table, click PowerPivot->Add to Date Model under home page.

 



2. Create a rank calculated column using the formula.

=RANKX(Table1,Table1[dept],,ASC)


3. Based on rank column value, calculate the expected result using formula.

=IF(AND(Table1[Rank]>=1,Table1[Rank]<=3),"X",IF(Table1[Rank]<=7,"Y","Z"))

 
Please see the result shown in following screenshot.



If you have other issues, don't hesitate to let me know.

Best Regards,
Angelia



  • Jawroy's avatar
    Jawroy
    Regular Visitor
    9 years ago

    Thanks Angelia