Changing hierarchy in visual gives wrong values

[KobeDel]

Hi all,

I have a column visual with a date hierarchy. When going deeper to the week filter instead of year by pushing one of those arrows, it's giving different data.

In the first picture, I get the right data. But the weeks are ordered from 1-52. I want them to be ordered first on year and than on week number. But that is giving me an infinity value.

Someone has an idea how to fix this?

Thanks in advance.

[v-jialongy-msft · Accepted solution]

Hi @KobeDel 

You can try checking the following points

1. Verify Hierarchy Setup: Ensure your date hierarchy is correctly set up in the model. Each level of the hierarchy (Year, Month, Week) should be correctly defined. 

2. Check Data Model Relationships: Ensure that the relationships between the tables (if your date fields are spread across different tables) are correctly established. Incorrect or missing relationships can lead to unexpected results when drilling down. 

 

You want the data to be ordered weeks first by year and then by week number, you might need to create a custom column that combines the year and week number into a single value that can be sorted numerically. Here's a simplified action plan:

1. Create a Custom Column: In Power BI Desktop, go to the Query Editor, and create a new custom column that concatenates the year and week number into a single value. For example, for year 2021 and week 5, the custom column value could be 202105. This ensures a sortable numeric value representing both the year and the week.

2. Sort by the Custom Column: Use this custom column as your sorting column in the visual. This approach ensures that your weeks are ordered first by year and then by week number, as intended.

Best Regards,

Jayleny

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

[KobeDel]

I made also a date hierarchy in the other two tables. This gave me the right data.

Thanks for the help!

[KobeDel]

If needed, I can shire the power BI file