Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
b2wise
Helper III
Helper III

How to compare measure results in different rows?

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.

 

ItemQty BreakProposed PriceNew Price
A-123      12.435.38
A-123      26.886.88
A-123      36.386.38
A-123      45.385.38
A-123      54.985.91
A-123      64.985.91
A-123      74.985.91
A-123      84.985.91
A-123      94.985.91
A-123     104.984.98
A-123     114.984.98
A-123     124.984.98
A-123     134.984.98
A-123     144.984.98
A-123     154.984.98
A-123     164.984.98
A-123     174.984.98
A-123     184.984.98
A-123     194.984.98
A-123     204.984.98

 

How do I do this?

 

Thanks in advance!

1 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.

vkalyjmsft_0-1661321577191.png

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.

View solution in original post

7 REPLIES 7
b2wise
Helper III
Helper III

@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
Helper III
Helper III

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

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.

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

 

 

 

 

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.

vkalyjmsft_0-1661321577191.png

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
Community Support
Community Support

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.

vkalyjmsft_0-1660806881711.png

After apply filter, get the correct result.

vkalyjmsft_1-1660806966923.png

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
Super User
Super User

First step would be to show your measure formula.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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