Forum Discussion

MCacc's avatar
MCacc
Icon for Helper IV rankHelper IV
3 years ago
Solved

Rankx Column with period column

Hello, 

 

I need the return two columns of a table in a tablix visual based on the max value of a month in a quarter:

 

Quarter is a filter the user can select, erby quarter has three months. The information my visual should display is the cod in column B e the value in column E for each max month for each quarter selected. 

The filter can be multi selection.

 

I created a RANK() column in the import a query, so for each code value I will have the max number for the months existing in the table.

 

Is there a way I can filter my visual using a calculated column where for each code value, I will have the information filtered for the max month in the selectable quarters?

I tried RANKX but, I fail to order my two columns in the right way..

 

Thank you very much for your help!

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  MCacc ,

    Here are the steps you can follow：

    1. Create measure.

    Flag =
VAR _SELECT=SELECTEDVALUE('Table'[QUARTER])
VAR _FLAG=MAXX(FILTER(ALL('Table'),[QUARTER]=_SELECT&&[CODE]=MAX('Table'[CODE])),[RANK])
RETURN
IF(
   MAX('Table'[RANK]) =_FLAG ,1,0)

    2. Place [Flag]in Filters, set is=1, apply filter.

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    3 years ago

    Hi MCacc ,

     

    If  you have a column wiht the MAX value of the months for each of the code you can try using a TOPN for each of the code something similar to this:

    MEtrica = TOPN(1, Table, Table[MaxMonth], DESC)

    Then when you add the code to your table it will return the values you need, be aware that you may need to add some additional sintax in order to get also the months filtered out, this depends on how you have you model setup.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  MCacc ,

    Here are the steps you can follow：

    1. Create measure.

    Flag =
VAR _SELECT=SELECTEDVALUE('Table'[QUARTER])
VAR _FLAG=MAXX(FILTER(ALL('Table'),[QUARTER]=_SELECT&&[CODE]=MAX('Table'[CODE])),[RANK])
RETURN
IF(
   MAX('Table'[RANK]) =_FLAG ,1,0)

    2. Place [Flag]in Filters, set is=1, apply filter.

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly