Rank
Hi
I'm looking to create a rank column as a measure so that I can then say x broker (in slicer) is ranked y out z as dynamic text.
I did attempt to use find row number by caption however the caption is hardcoded so wouldn't give me what I needed.
Is there a way to use filter / variable / param as caption or if not then is there an alternative?
Thanks
Nick
8 replies
-
What type of data source are you using?
-
It's a SQL database with select and SQL Queries.
I looked and suspect I could do this with an OLAP source although we have no cubes nor used MDX before however if this is something you'd recommend then I'm happy to give it a go. I'm sure I can work it out.
-
Nicholas Campbell
May I ask you, what was/is the reason for that you have not used OLAP cubes until now?
-
Never had to, I'd use stored procedures for SSRS, or use tabular models for PowerPivot (and use DAX).
-
Hi Nick,
We do not yet have a Rank function in PQL, but it should be coming soon.
I will try to think of an alternative way to solve that question.
In the mean while, If you have the option to use a tabular model, as you are familiar with those, you could connect from Pyramid to that model and use the MDX Rank function.
If that might work for you, we could also help with the MDX syntax to get the desired result.
Imbar-
imbar Any update on estimated arrival time for a Rank function? "soon" was nebulous, but posted 5 months ago I'd feel like we're getting "sooner"
-
Hi Philip Augur
Still haven't got to this function.You could add it to the feature requests and get up-votes on it to move it up the backlog.
-
-
Hi Nicholas,
As Imbar says, with regards to the Rank() function, this is currently only available within the “Common” functions, not the semantic model functions. This means that while it may be used in Model and Dynamic Text calculations, it currently cannot be used in conjunction with semantic model calculations, handling sets from the model definition. This is some thing we will be supporting in a future release.
However, I did create a workaround as follows. In this example I am using our demo database.
- Create a sorted set of all the members of the desired attribute. In the example below this is Manufacturers, sorted high to low based on the desired measure, in this case sales.
- Create a calculated measure (RankCalc) using the PQL as follows (the item {[manufacturers].[Manufacturer].*[5d6c8ee2-c8b2-428e-b488-609a7625915b]} is the sorted set):
Case(criteria(
currentmember([manufacturers].[Manufacturer])=GetElement({[manufacturers].[Manufacturer].*[5d6c8ee2-c8b2-428e-b488-609a7625915b]}, 0),
currentmember([manufacturers].[Manufacturer])=GetElement({[manufacturers].[Manufacturer].*[5d6c8ee2-c8b2-428e-b488-609a7625915b]}, 1),
currentmember([manufacturers].[Manufacturer])=GetElement({[manufacturers].[Manufacturer].*[5d6c8ee2-c8b2-428e-b488-609a7625915b]}, 2),
currentmember([manufacturers].[Manufacturer])=GetElement({[manufacturers].[Manufacturer].*[5d6c8ee2-c8b2-428e-b488-609a7625915b]}, 3),
currentmember([manufacturers].[Manufacturer])=GetElement({[manufacturers].[Manufacturer].*[5d6c8ee2-c8b2-428e-b488-609a7625915b]}, 4),
currentmember([manufacturers].[Manufacturer])=GetElement({[manufacturers].[Manufacturer].*[5d6c8ee2-c8b2-428e-b488-609a7625915b]}, 5),
currentmember([manufacturers].[Manufacturer])=GetElement({[manufacturers].[Manufacturer].*[5d6c8ee2-c8b2-428e-b488-609a7625915b]}, 6),
currentmember([manufacturers].[Manufacturer])=GetElement({[manufacturers].[Manufacturer].*[5d6c8ee2-c8b2-428e-b488-609a7625915b]}, 7),
currentmember([manufacturers].[Manufacturer])=GetElement({[manufacturers].[Manufacturer].*[5d6c8ee2-c8b2-428e-b488-609a7625915b]}, 8),
currentmember([manufacturers].[Manufacturer])=GetElement({[manufacturers].[Manufacturer].*[5d6c8ee2-c8b2-428e-b488-609a7625915b]}, 9),
currentmember([manufacturers].[Manufacturer])=GetElement({[manufacturers].[Manufacturer].*[5d6c8ee2-c8b2-428e-b488-609a7625915b]}, 10)),
criteriaresults(
1,2,3,4,5,6,7,8,9,10,11),
0)
3. Create the view showing the RankCalc value:
This does require you to know the number of items in the list to be ranked, but that could be calculated dynamically as well.
Hope that helps.
Ian
- Create a sorted set of all the members of the desired attribute. In the example below this is Manufacturers, sorted high to low based on the desired measure, in this case sales.