cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

Comparing next row data and calculating MOD

Hi,

I am trying to compare the value of Product ID with the next cell in the table and performing some If conditions. But my logic doesn't work.

Here is my Code

```Column = Var CurentPID =Sheet1[Product ID]
Var NextPID=Sheet1[Product ID]+1
Var CurrentNumber =Sheet1[Number]
Var NextNumber=Sheet1[Number]+1

Return
If(Sheet1[Product ID]=LOOKUPVALUE(Sheet1[Product ID],Sheet1[Index],Sheet1[Index]+1),
if(or(CurrentNumber=1,NextNumber=1),"NA",
if(Mod(CurrentNumber,NextNumber)=0,"OK","FALSE"))
)```

My data:

 Product ID Number 11111 10000 11111 1000 11111 1000 11111 1 22222 500 22222 100 22222 1 33333 1

My output with Excel formula =if(B2=1,"na"IF(and(B2=1,B3=1),"na",IF(MOD(B2,B3)=0,"OK","False")))

((However now in Power BI I also want to put a check to see if the product ID in the next cell is same, if not same then if number is 1 then NA))

 Product ID Number Output 11111 10000 OK 11111 1000 OK 11111 1000 OK 11111 1 na 22222 500 OK 22222 100 OK 22222 1 na 33333 1 na

Any help is appreciated!

Thanks,

Tejaswi

2 ACCEPTED SOLUTIONS
Super User

Hello @Anonymous

This worked for me on your sample data, give it a try.

```Column =
VAR _PreviousAmount =
CALCULATE(
SUM ( Sheet1[Number] ),
ALLEXCEPT ( Sheet1, Sheet1[Product ID] ),
Sheet1[Index] = Sheet1[Index] - 1)
VAR _MOD = MOD(_PreviousAmount,Sheet1[Number])
RETURN
SWITCH (
TRUE(),
Sheet1[Number] = 1, "na",
_MOD = 0, "OK",
"False"
)```
Super User

Gotcha, just change it to this:

```Column =
VAR _Index = 'Sheet1'[Index]
VAR _NextAmount =
CALCULATE(
SUM ( 'Sheet1'[Number] ),
ALLEXCEPT ( 'Sheet1', 'Sheet1'[Product ID] ),
'Sheet1'[Index] = _Index + 1)
VAR _MOD = IF ( ISBLANK(_NextAmount),0,MOD('Sheet1'[Number],_NextAmount))
Return
SWITCH (
TRUE(),
'Sheet1'[Number] = 1, "na",
_MOD = 0, "OK",
"False"
)```

11 REPLIES 11
Anonymous
Not applicable

Hi,

Any help greatly appreacited!

-Tejaswi

Super User

Hello @Anonymous

This worked for me on your sample data, give it a try.

```Column =
VAR _PreviousAmount =
CALCULATE(
SUM ( Sheet1[Number] ),
ALLEXCEPT ( Sheet1, Sheet1[Product ID] ),
Sheet1[Index] = Sheet1[Index] - 1)
VAR _MOD = MOD(_PreviousAmount,Sheet1[Number])
RETURN
SWITCH (
TRUE(),
Sheet1[Number] = 1, "na",
_MOD = 0, "OK",
"False"
)```
Anonymous
Not applicable

I found the below issue when I added this code in a actual file.

When I used this in my actual data , it should appear false if the mod is not zero however it still shows OK.

For example : Product ID 44444 , Number is 110 and the next number is 12, the Mod of these two numbers 110/12 is non zero still the output shows OK, Could you please help tweak your code?

Thanks,

Tejaswi

Super User

@Anonymous

Try changing it to this

```Column =
VAR _Index = Sheet1[Index]
VAR _PreviousAmount =
CALCULATE(
SUM ( Sheet1[Number] ),
ALLEXCEPT ( Sheet1, Sheet1[Product ID] ),
Sheet1[Index] = _Index - 1)
VAR _MOD = MOD(_PreviousAmount,Sheet1[Number])
RETURN
SWITCH (
TRUE(),
Sheet1[Number] = 1, "na",
_MOD = 0, "OK",
"False"
)```

The MOD was off because the row index was not getting picked up.

Anonymous
Not applicable

But now it shows FALSE for all the OKs aswell.

Super User

This does not match what my results are:

```Column =
VAR _Index = 'Sheet1'[Index]
VAR _PreviousAmount =
CALCULATE(
SUM ( 'Sheet1'[Number] ),
ALLEXCEPT ( 'Sheet1', 'Sheet1'[Product ID] ),
'Sheet1'[Index] = _Index - 1)
VAR _MOD = MOD(_PreviousAmount,'Sheet1'[Number])
RETURN
SWITCH (
TRUE(),
'Sheet1'[Number] = 1, "na",
_MOD = 0, "OK",
"False"
)```

Anonymous
Not applicable

yes I made a mistake while copy pasting and renaming the the actual field name.  I get the results what you get .

However can we have the highest number as false. In you case it shows 12 as false. I want highest number 110 false if the mod is non zero and 12 as OK

Thanks,

Tejaswi

Super User

Gotcha, just change it to this:

```Column =
VAR _Index = 'Sheet1'[Index]
VAR _NextAmount =
CALCULATE(
SUM ( 'Sheet1'[Number] ),
ALLEXCEPT ( 'Sheet1', 'Sheet1'[Product ID] ),
'Sheet1'[Index] = _Index + 1)
VAR _MOD = IF ( ISBLANK(_NextAmount),0,MOD('Sheet1'[Number],_NextAmount))
Return
SWITCH (
TRUE(),
'Sheet1'[Number] = 1, "na",
_MOD = 0, "OK",
"False"
)```

Anonymous
Not applicable

Sorry  I made a typo while copy pasting the data.

Its close enough but can we have the 110 as False? The highest number to be false and other is OK

Anonymous
Not applicable

This worked as expected.

Thanks,

Tejaswi

Solution Sage

I think the standard advice in Power BI is to do these kinds of calculations in Excel. Excel is made for doing things involving adjacent cells and rows. Power BI is not. You can do it in Power BI but its complicated and you end up with slow measures.

Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!

Join the conversation at We Talk BI find out more about me at Slow BI

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors