cancel
Showing results 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

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.

 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?

1 ACCEPTED SOLUTION
Community Support

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.

7 REPLIES 7
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!

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.

Community Support

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.

Helper III

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

Community Support

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.

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.

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.

Super User

First step would be to show your measure formula.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors