# Number of cases equal to the maximum and minimum value

### Question

• I have a list consisting of Item, Price and Date. My objective is to figure out:

• How many records for each item
• How many different prices
• How many prices are equal to the max value of each item

Calculating the number of records for each item item is easy as calculating the number of different values. I haven’t been able to solve the number of item equal to the maximum or minimum value. I haven’t figured out how I can use the result of a measure to filter the records I want. If I put the value manually in the measure it will give the right result (number of records equal to the value), but I need this to be dynamic. Also I want to be able to slice data by date otherwise I could do an approach by calculated column.

Can anyone help me figure this out?

Wednesday, March 06, 2013 9:47 AM

• Hello Pedro ,

I couldnt find a way to use a measure in another measure. But this can be achived by this workaround approach.

For the below , i have created a table test with (Item , Date & Price columns)

1. Create a caluclated column "MaxVal" with below expression

`=maxx(filter(test, EARLIER(test[Item]) = test[Item] ),test[Price])`

2. Create a measure with below expression

`Measure 2:=countx(filter(test,test[Price] = test[MaxVal]),test[Price])`

Best Regards Sorna

Thursday, March 07, 2013 10:44 AM

### All replies

• Hello Pedro,

You have not specified in which you need to do this calculation  SSRS or Power Pivot or in your cube ?  Please provide more details for further assistance.

Best Regards Sorna

Wednesday, March 06, 2013 1:52 PM
• Hello Sorna,

I'm working with Powerpivot running on Excel 2010.

Hope you can help,

Pedro

Wednesday, March 06, 2013 5:16 PM
• Hello Pedro ,

I couldnt find a way to use a measure in another measure. But this can be achived by this workaround approach.

For the below , i have created a table test with (Item , Date & Price columns)

1. Create a caluclated column "MaxVal" with below expression

`=maxx(filter(test, EARLIER(test[Item]) = test[Item] ),test[Price])`

2. Create a measure with below expression

`Measure 2:=countx(filter(test,test[Price] = test[MaxVal]),test[Price])`

Best Regards Sorna

Thursday, March 07, 2013 10:44 AM
• Hello Sorna,

The calculated column approach is something I said I wanted to avoid because it will not let me slice data by date. I need a
solution that can analyze max and min values in the current filter context. <o:p></o:p>

Let’s see if anyone can shed some light on how this can be achieved or if the answer is that it is not possible to have a measure use a value of another measure as a filter.

Thursday, March 07, 2013 9:37 PM