Comparing next row data and calculating MOD

[Anonymous]

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

[jdbuchanan71 · Accepted solution]

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]

Hi,

@jdbuchanan71, @Zubair_Muhammad, @Sean, @TomMartens

Any help greatly appreacited!

-Tejaswi

[jdbuchanan71 · Accepted solution]

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]

HI @jdbuchanan71 ,

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.

Can you please help me?

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

[jdbuchanan71]

@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]

HI @jdbuchanan71 ,

Thanks for your reply!

But now it shows FALSE for all the OKs aswell.

[jdbuchanan71]

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]

@jdbuchanan71 ,

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

Appreacite all your kind help!

Thanks,

Tejaswi

[jdbuchanan71 · Accepted solution]

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]

@jdbuchanan71 ,

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]

Thanks @jdbuchanan71  for your quick reply and solutions.

This worked as expected.

Thanks,

Tejaswi

[kentyler]

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.