Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Syndicate_Admin
Administrator
Administrator

query!, dynamic order of weeks according to filter

Hello Community

I have the concern to know if there is any way to order weeks according to the value of a column (in this case vegetables), for example the avocado season let's say that it starts week 30 and ends week 29 of the following year, I understand that I can create an order table for that and until there it did not open problem, BUT, now I would like that when using segmentation to select another species for example Cherries, the visualizations were ordered from week 13 to 12 of the following year.

Your help is appreciated!

1 REPLY 1
123abc
Community Champion
Community Champion

In Power BI, you can achieve dynamic sorting of weeks based on the selected filter using DAX (Data Analysis Expressions) and measures. Here's a step-by-step guide on how to do it:

  1. Create a Date Table: Ensure you have a Date table in your Power BI model with a column for weeks. You can create this manually or use Power Query to generate it based on your data.

  2. Create a Measure for Week Start: Create a DAX measure that calculates the starting week for each vegetable season based on your filtering criteria. For example, if you want Avocado to start from week 30, create a measure like this:

Avocado Start Week = IF(SELECTEDVALUE('Vegetables'[Vegetable]) = "Avocado", 30, IF(SELECTEDVALUE('Vegetables'[Vegetable]) = "Cherries", 13, 1))

 

  1. In this measure, we use the SELECTEDVALUE function to determine which vegetable is selected in the filter and assign the appropriate start week accordingly.

  2. Create a Sorting Measure: Next, create a sorting measure that combines the start week with the week number. This measure will be used for sorting your visuals.

Sorting Week = [Avocado Start Week] + 'Date Table'[WeekNumber]

 

  1. This measure adds the selected vegetable's start week to the week number from your Date table.

  2. Apply Sorting Measure to Visuals: Now, apply the Sorting Week measure to the "Sort by column" property of your visualizations. This will dynamically sort the weeks based on the selected vegetable.

    • Select the visualization.
    • Go to the "Format" pane.
    • Expand the "Sort by column" property.
    • Choose the Sorting Week measure.
  3. Test with Slicers or Filters: Add slicers or filters for the vegetable column (e.g., Avocado and Cherries). When you change the filter, the sorting of weeks in your visualizations will adjust accordingly based on the selected vegetable.

By following these steps, you can dynamically order weeks in your Power BI visuals based on the selected vegetable species, making your visualizations adapt to different filter selections.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.