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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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