0

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

null
    • "making the sophisticated simple"
    • AviPerez
    • 4 yrs ago
    • Reported - view

    What type of data source are you using?

    • Nicholas_Campbell
    • 4 yrs ago
    • Reported - view

    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.

    • Senior Consultant BI & Data Management
    • Markus_Loy
    • 4 yrs ago
    • Reported - view

    Nicholas Campbell 

    May I ask you, what was/is the reason for that you have not used OLAP cubes until now?

    • Nicholas_Campbell
    • 4 yrs ago
    • Reported - view

    Never had to, I'd use stored procedures for SSRS, or use tabular models for PowerPivot (and use DAX). 

    • imbarmarinescubar_pyram
    • 4 yrs ago
    • Reported - view

    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

      • Philip_Augur
      • 4 yrs ago
      • Reported - view

      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"  😉

      • imbarmarinescubar_pyram
      • 4 yrs ago
      • Reported - view

      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.

    • Senior Director of Product
    • Ian_Macdonald
    • 4 yrs ago
    • Reported - view

    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.

    1. 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.
    2. 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

Content aside

  • Status Answered
  • 4 yrs agoLast active
  • 8Replies
  • 90Views
  • 6 Following