Rankx Column with period column

[MCacc]

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!

[v-yangliu-msft · Accepted solution]

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

[MFelix]

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.