How to compare measure results in different rows?

[b2wise]

Hi all,

I am using a formula called "New Price" to calculate item prices at various quantity breaks (discount for buying an item in bulk).

The issue is that sometimes my formula returns a higher price for a greater qty break (for the same item) than the price for a  smaller qty break. I would like to filter out any "New Price" which doesn't make sense.

So if the price at qty break 5 is higher than the price at qty break 1 filter out the qty 5 price.

The first 3 columns in this table visual are dataset columns and the fourth is a DAX measure.

Item	Qty Break	Proposed Price	New Price
A-123	1	2.43	5.38
A-123	2	6.88	6.88
A-123	3	6.38	6.38
A-123	4	5.38	5.38
A-123	5	4.98	5.91
A-123	6	4.98	5.91
A-123	7	4.98	5.91
A-123	8	4.98	5.91
A-123	9	4.98	5.91
A-123	10	4.98	4.98
A-123	11	4.98	4.98
A-123	12	4.98	4.98
A-123	13	4.98	4.98
A-123	14	4.98	4.98
A-123	15	4.98	4.98
A-123	16	4.98	4.98
A-123	17	4.98	4.98
A-123	18	4.98	4.98
A-123	19	4.98	4.98
A-123	20	4.98	4.98

How do I do this?

Thanks in advance!

[b2wise]

@v-yanjiang-msft This works!! Thanks so much!

My error was writing the qty break filter with max (and not using IN)

Thanks again!

[b2wise]

@v-yanjiang-msft Perfect! Can I trouble you to help me tweak this formula so it excludes certain qty breaks entirely?

For example, I want to ignore qty breaks 13 thru 19 in all situations so even if their price is lower than qty break 20 still show me the "New Price" for qty break 20.

[v-yanjiang-msft]

Hi @b2wise ,

If you want the value from break 13 to break 19 always display anyway, modify the formula like this:

Check =
IF (
    MAX ( 'Table'[Qty Break] ) >= 13
        && MAX ( 'Table'[Qty Break] ) <= 19,
    1,
    IF (
        [New Price]
            = MINX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Item] = MAX ( 'Table'[Item] )
                        && 'Table'[Qty Break] <= MAX ( 'Table'[Qty Break] )
                ),
                [New Price]
            ),
        1,
        0
    )
)

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[b2wise]

@v-kalyj-msft Sorry I wasn't clear.

I want MINX to find the minimum price at <=Qty Break but to ignore prices of 13 through 19. In other words, when evaluating at qty break 20 check to see if there was a lower price at "New Price" of 1 thru 12 only.

This is what I tried writing but it's not working.

IF(
[New Price]
= MINX (
FILTER (
ALL ( 'Table' ),
'Table'[Item] = MAX ( 'Table'[Item] )
&& 'Table'[Qty Break] <= MAX ( 'Table'[Qty Break]) && MAX('Table'[Qty Break])<> 11 && MAX('Table'[Qty Break])<> 13 && MAX('Table'[Qty Break])<> 14 && MAX('Table'[Qty Break])<> 15 &&
MAX('Table'[Qty Break])<> 16 && MAX('Table'[Qty Break])<> 17 && MAX('Table'[Qty Break])<> 18 && MAX('Table'[Qty Break])<> 19
),
[New Price]
),
1,
0
)

[v-yanjiang-msft · Accepted solution]

Hi @b2wise ,

Sorry I misunderstood it. Modify the formula to:

Check =
IF (
    [New Price]
        <= MINX (
            FILTER (
                ALL ( 'Table' ),
                NOT ( 'Table'[Qty Break] IN { 13, 14, 15, 16, 17, 18, 19 } )
                    && 'Table'[Item] = MAX ( 'Table'[Item] )
                    && 'Table'[Qty Break] <= MAX ( 'Table'[Qty Break] )
            ),
            [New Price]
        ),
    1,
    0
)

I change the data and the Qty Break 20 still display as expected.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yanjiang-msft]

Hi @b2wise ,

According to your description, here's my solution, create a measure.

Check =
IF (
    [New Price]
        = MINX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Item] = MAX ( 'Table'[Item] )
                    && 'Table'[Qty Break] <= MAX ( 'Table'[Qty Break] )
            ),
            [New Price]
        ),
    1,
    0
)

Put the measure in the visual filter and select its value to 1.

After apply filter, get the correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

First step would be to show your measure formula.