Wednesday, March 06, 2013 9:47 AM
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 1:52 PM
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 5:16 PM
I'm working with Powerpivot running on Excel 2010.
Hope you can help,
Thursday, March 07, 2013 10:44 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 9:37 PM
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.