Dimension based on another dimension

[luizcdepaula]

Hello experts,

I am having a challenge trying to figure this out for a dimension value. I am working on a inventory report, where we need to list the primary supplier for each material. Some materials might have 2 or 3 different suppliers, so they just want the supplier that sold the most in the year to be displayed. I was able to achieve that by using FirstSortedValue function, based on total quantity purchased for the year.

The problem I am facing now, is that some materials are not used as often, and they are purchased every 2 or 3 months. So, as I am pulling the suppliers based the total quantity purchased on the year, in January and February I am getting a lot of nulls. So, they asked I can use the previous year primary supplier if the current year primary supplier is NULL.

So, I need to create a statement, with a condition that says, if primary supplier (which is defaulted for current year) is null, give me primary supplier from last year. This could easily be accomplished with set analysis if it was a measure, but I have never done it for a dimension.

Find an example below

How it is now                                                                    

How it is supposed to be

Thank you in advance,

LD

[luizcdepaula]

Hi Veeranjaneyulu.

I was able to fix it, or at least make it work with a similar solution, by creating another table, with previous year top suppliers, and then changing the year to current year, and calling it Primary_Supplier_PY.

However, I do like your approach, using the the applymap function, as it works for previous years too, without having to create different tables for each year.

Great work!

Thanks,

LD

[Anonymous]

basically this should work, but I think it is not worth trying any further, because you have a working solution...