Problems with change the paramters after the new year

[Portrek]

Hello, everyone. I have a question.

I have a database with information from the year 2020 where several items belong to certain boxes, as shown in image 1

It turns out that from 01/012021 the items were redistributed as shown in image 2 below

I have an application that shows the number of items month by month in each box and the number of items according to the box in a table, but with this change the Power bi was lost all in the calculations.

What I would like to do: that according to the selected box the table would bring me the items (in order) in the table lines considering the selectvalue year of the year in question

I put a model, so maybe you can help me solve this problem ..

https://we.tl/t-YMJp0scv1M

I thank everyone who collaborates

[v-cazheng-msft]

Hi, @Portrek 

According to your newly description, I've made some changes to your pbix file. Here is the sample.

For regulating the order of the items, you can refer Sort a Column with a Custom Order in Power BI.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Portrek]

Hello, Caiyun Zheng

It didn't work out, so I've been searching I have to create a unique key, considering the boxes and items, for the records. Even so, I thank you for the return. The ordering part worked perfectly. Thanks.

[mahoneypat]

The table will only show (1) combinations that exist in your data and (2) those that have non-blank results.  You can address the 2nd one by adding 0 to your measure, so that none return blank.

Sum + 0 = SUM(Registros[Qtde.]) + 0

However, you don't have rows for all Items in every month and box.  If you add another dimension table that has all the item/group/year combinations, you could use that in the visual with the +0 measure to see a 0 or a value for every month/item combination.

Pat

[lbendlin]

Combine these two tables (append) into one dimension table.

Create a composite key "YearItem" in this table, and in your fact table.

Link the dimension and fact tables via the composite key.

[Portrek]

Hello Ibendlin, thanks for your help.

I already made this type of link but when I put the values of the items in the headers - line of a table it gets lost, for example when I select the year 2020 - BOX1, the lines should bring me Items from A1 to A8 and in the case of 2021 - BOX1 the lines from A1 to A11, and the same should happen with the selection of boxes2 and 3, but it is getting lost in the filtering and ordering of this data ..