Excel measure to Powerbi

[SpiroswayGR]

Dear community,

I have simple request about calculate this in powerbi.

=IF(J5=J4;IF(K5<K4;1;0);0)

code	price	calculation
32751	344,00	0,00
32751	284,80	1,00
32751	284,80	0,00
32748	256,00	0,00
32748	256,00	0,00

thank you in advance

kind regards,

Spyros

[Fowmy · Accepted solution]

@SpiroswayGR 

Please check this measure, I also attached the updated file.

Result 2 = 

var __price = MAX(Table2[price])
var __value = MAX(Table2[Value])
var __index = MAX(Table2[Index])
var __code= MAX(Table2[Code])

return
INT(
    __price < 
    CALCULATE(
        MAX(Table2[price]),
        Table2[Index] < __index,
        Table2[Value] = __value,
        Table2[Code] = __code,
        REMOVEFILTERS(Table2)
    )
)

[Fowmy]

@SpiroswayGR 

You can create the following measure: I attached a sample PBIX file below my signature.

Result =
VAR __price =
    MAX ( Table1[price] )
VAR __index =
    MAX ( Table1[index] )
RETURN
    INT (
        __price
            < CALCULATE (
                MAX ( Table1[price] ),
                Table1[Index] = __index - 1,
                ALLEXCEPT ( Table1, Table1[code] )
            )
    )

[SpiroswayGR]

@Fowmy  Thank you for your time.

Looks like not working for me , maybe cause i didn't share correct data.

In Powerbi i have the same data but with unpivot , cause 1 line has gross price and 2nd line has net value and i am using filter to show different value.

Gross value should be compared vs below GROSS value (not with net). When i recreate a table to visualize it i am using code and date and i am filtering only GROSS value or net so it should work.

code     price   value_status    Date                   Index          new column

30000   124      gross            01/06/2021             1               0

30000   100      net               01/06/2021            2                 0

30000   115     gross           02/06/2021            3                   1

30000   94       net               02/06/2021           4                    1 

30000   124      gross           03/06/2021            5                  0

30000   100      net              03/06/2021             6                  0

25000   248     gross           06/07/2021             7                  0

25000   200     net               06/07/2021             8                 0

25000   248     gross           06/07/2021             9                  0

25000   200     net               06/07/2021             10                0

25000   220     gross           06/07/2021             11                1

25000   195     net               06/07/2021             12               1

thank you again

[Fowmy · Accepted solution]

@SpiroswayGR 

Please check this measure, I also attached the updated file.

Result 2 = 

var __price = MAX(Table2[price])
var __value = MAX(Table2[Value])
var __index = MAX(Table2[Index])
var __code= MAX(Table2[Code])

return
INT(
    __price < 
    CALCULATE(
        MAX(Table2[price]),
        Table2[Index] < __index,
        Table2[Value] = __value,
        Table2[Code] = __code,
        REMOVEFILTERS(Table2)
    )
)

[SpiroswayGR · Accepted solution]

@Fowmy 

This might work, but i am trying to test it at table without unpivot so i recreate the dax script.

The problem is that solution might be so slow cause if i filter only 1 day from year need around 40-60 seconds and with 2-3 different filters to decrease the amount of rows.

I was trying to perform test in 1 month data , let's say around 10.000 rows but still loading even after 15-20 minutes or more.

This is so simple in excel and so fast but in pbi looks so slow. First time i see that.

Thanks again for your time