Forum Discussion
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.
link to my file-- https://www.dropbox.com/s/grxqnjtmyczhbdl/Sample%20file.pbix?dl=0
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
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" )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
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.
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" )
