Forum Discussion

nhniniras's avatar
nhniniras
Frequent Visitor
3 years ago
Solved

RANKX on selected field parameter

Is there anyone who have succeded creating a dynamic RANKX based on a Parameter Field selection? Today I have a this "static" Rank measure: Rank BU and Gross Revenue = RANKX(all(DimOrganisation[...
  • v-easonf-msft's avatar
    v-easonf-msft
    3 years ago

    Hi， nhniniras 

    Are you referring to this kind of ‘Field-parameters’ ?

    Currently, PowerBI does not support direct use of field parameters in measure.

     

    Best Regards,
    Community Support Team _ Eason

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hello nhniniras ,

     

    I was also looking a solution for this in the web, and then I realized that "field parameter" is only for Power Bi to understand it, but at the end is about the columns you have included in your fields.

     

    So what I did is to created a measure with an if statement according to the Order of my fields names and is working perfectly (I used the measure to show a specific number of customers in a visual according to a slicer, hence the switch true):

    IF(SELECTEDVALUE(localCustomers[localCustomers Order]) = 0
        ,SWITCH(TRUE()
            ,RANKX(
                ALLSELECTED('Dim Customer'[LOCAL_CUSTOMER_NAME])
                ,[businessMetricCP]
                ,,,Dense
            ) <= SelectedTop,
            1
        )
        ,SWITCH(TRUE()
            ,RANKX(
                ALLSELECTED('Dim Customer'[CUSTOMER_KEY])
                ,[businessMetricCP]
                ,,,Dense
            ) <= SelectedTop,
            1
        )
    )
     
    Hope can help others with the same doubts.
v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi， nhniniras 

Are you referring to this kind of ‘Field-parameters’ ?

Currently, PowerBI does not support direct use of field parameters in measure.

 

Best Regards,
Community Support Team _ Eason

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hello nhniniras ,

 

I was also looking a solution for this in the web, and then I realized that "field parameter" is only for Power Bi to understand it, but at the end is about the columns you have included in your fields.

 

So what I did is to created a measure with an if statement according to the Order of my fields names and is working perfectly (I used the measure to show a specific number of customers in a visual according to a slicer, hence the switch true):

IF(SELECTEDVALUE(localCustomers[localCustomers Order]) = 0
    ,SWITCH(TRUE()
        ,RANKX(
            ALLSELECTED('Dim Customer'[LOCAL_CUSTOMER_NAME])
            ,[businessMetricCP]
            ,,,Dense
        ) <= SelectedTop,
        1
    )
    ,SWITCH(TRUE()
        ,RANKX(
            ALLSELECTED('Dim Customer'[CUSTOMER_KEY])
            ,[businessMetricCP]
            ,,,Dense
        ) <= SelectedTop,
        1
    )
)
 
Hope can help others with the same doubts.
  • nhniniras's avatar
    nhniniras
    Frequent Visitor
    3 years ago

    Hi and sorry for my late respons, but thank you very much.
    I think we more or less ended up with the same solution with some IF and Swithc functions 🙂

    In my solution I ended up writing a bunch of measures and used the Switch function to find the "parameter" measure the user wants create a Top X on.

     
    ------- 1. My Switch function
    Measure TopX selected =
    SWITCH (
        SELECTEDVALUE ( 'ParameterTopX'[Parameter_TopX Fields] ),
        "'ALL_Measures'[Top X BU]",
            [Top X BU]
                / CALCULATE (
                    [Measure selected],
                    ALLSELECTED ( DimOrganisation_project[BU_Initial] )
                ),
        "'ALL_Measures'[Top X Company]",
            [Top X Company]
                / CALCULATE (
                    [Measure selected],
                    ALLSELECTED ( DimOrganisation_project[ReportCompanyDescription] )
                ),
        "'ALL_Measures'[Top X Customer]",
            [Top X Customer]
                / CALCULATE ( [Measure selected], ALLSELECTED ( Customer_Bridge[Customer] ) ),
     
    ---- 2. create measures for all "parameter" measures used in Switch function to filter Top X (number of Top X ranks the user wants to see)
    Top X BU =
    IF ( [Rank BU] <= 'TopXSeries'[TopXValue], [Measure selected] )
     
    ---- 3. create the Rank function
    Rank BU =
    IF (
        HASONEVALUE ( DimOrganisation_project[BU_Initial] ),
        RANKX (
            ALLSELECTED ( DimOrganisation_project[BU_Initial] ),
            [Measure selected]
        )
    )
     
     I don't know if it could have been made easier, but it works as it should - so thank you all for your input.