topic Re: Rank function on selected dates in DAX Commands and Tips

Rank function on selected dates

llealsantos — Fri, 05 Jul 2024 16:33:56 GMT

We need to apply conditional formatting to identify the top 5 and bottom 3 values in a dataset, based on user-selected fiscal periods. The selection can include non-continuous fiscal periods, and the ranking should be recalculated to ignore any periods that are not selected.

Users can select any combination of fiscal periods. This selection can include continuous periods (e.g., 2024 P05, 2024 P04, 2024 P03) or non-continuous periods (e.g., 2024 P04, 2023 P11, 2022 P03).

The ranking should dynamically adjust based on the selected periods. For example, if the user selects 2024 P05, 2023 P05, and 2022 P05, the ranks should be recalculated as 1, 2, and 3 based on the values for these periods alone, not the entire dataset. This ranking should ensure that the top 5 and bottom 3 values are identified correctly based on the user's selection.

To represent the top 5 and bottom 3 values I will use conditional formatting, so after ranking the periods correctly, it should return 1 for the top 5 values and 2 for the bottom 3.

The fiscal periods are stored in the Calendar[Year Period] column.

The format of fiscal periods is YYYY PXX, for example, 2024 P05 for the 5th period of the year 2024.

We have a Fiscal Calendar Table that has columns for Year, Beginning Date, Ending Date and Period Number.
We have measures for all KPIs.

Is this possible?

Re: Rank function on selected dates

foodd — Sat, 06 Jul 2024 17:43:13 GMT

Hello llealsantos , remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

Re: Rank function on selected dates

Anonymous — Sun, 07 Jul 2024 04:55:34 GMT

please check the rankx function what I have used on the dataset.

Please check the final output.

If you think that this helps you then accept this as your solution.

Re: Rank function on selected dates

Jihwan_Kim — Sun, 07 Jul 2024 05:13:16 GMT

Hi,

I do not know how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

RANK function (DAX) - DAX | Microsoft Learn

WINDOW function (DAX) - DAX | Microsoft Learn

Sales: = SUM(sales[sales])

Rank sales: = RANK ( SKIP, SUMMARIZE ( ALLSELECTED ( sales ), sales_team[sales_team] ), ORDERBY ( CALCULATE ( SUM ( sales[sales] ) ), DESC ) )

Sales rank top5 and bottom3 = VAR _top5 = WINDOW ( 1, ABS, 5, ABS, SUMMARIZE ( ALLSELECTED ( sales ), sales_team[sales_team] ), ORDERBY ( CALCULATE ( SUM ( sales[sales] ) ), DESC ) ) VAR _bottom3 = WINDOW ( 1, ABS, 3, ABS, SUMMARIZE ( ALLSELECTED ( sales ), sales_team[sales_team] ), ORDERBY ( CALCULATE ( SUM ( sales[sales] ) ), ASC ) ) VAR _teamlist = UNION ( _top5, _bottom3 ) RETURN CALCULATE ( SUM ( sales[sales] ), KEEPFILTERS ( sales_team[sales_team] IN _teamlist ) )

Re: Rank function on selected dates

llealsantos — Mon, 08 Jul 2024 09:13:25 GMT

Thank you!! It was that ALLSELECTED that was missing

Re: Rank function on selected dates

llealsantos — Mon, 08 Jul 2024 09:13:47 GMT

Thank you so much!! I was missing that ALLSELECTED

Re: Rank function on selected dates

llealsantos — Mon, 08 Jul 2024 09:15:56 GMT

Unfortunately, I don't have time to do all that. Thank you for your useful reply.