How to create a hierarchy based on 2 multiple select lists

Hello,

I am displaying a treemap with a company structure (BU - Departement - Client) and I have a list box where end users can select any of the 3 company elements (BU, Departement and/or Client) so the treemap has a dynamic hierarchy.

It is working perfectly fine with the custom expression <$map("$esc(${SelectHierarchy})", " nest")>.

But the BU organisation has changed in 2019, and there is now a column for BU 2018 and a column for BU 2019 so that we can check figures based on 2018 or 2019 structure.

So I need to mix 2 multiple select lists to construct the treemap hierarchy

- one for the year (2018 and/or 2019) ----- > as soon as year 2019 is in the selection, BU 2019 should be used and not BU 2018.

- one for the company structure (BU, Departement and/or Client) ---> BU refers to BU 2018 in the list box.

 

Unfortunately, I am not able to make the custom expression work, it should be close to :

< case when Find("2019","${SelectYear}") >= 1 and Find("BU","${SelectHierarchy}") >= 1 
then Substitute("$map("$esc(${SelectHierarchy})", " NEST ")","BU 2018", "BU 2019")
else $map("$esc(${SelectHierarchy})", " nest")
end  as [Hierarchy]>

Can anyone help me ? I am attaching a dashboard as an example.

Thanks

Attachments

Login