Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jawroy
Regular Visitor

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] has 8 unique values(A, B, C, D, E, F, G, H),  which I want to categorize in 3 categories X, Y and Z. For example, A through C go in X, D through F go in Y, what's left in Z.

 

How would I handle this in Powerpivot?

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

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.

 

1.PNG

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.

2.PNG

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

Best Regards,
Angelia



View solution in original post

2 REPLIES 2
v-huizhn-msft
Employee
Employee

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.

 

1.PNG

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.

2.PNG

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

Best Regards,
Angelia



Thanks Angelia

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.