For each company, we assigned one sector via Boolean. For example in the image, beflex electronic GmbH is “Mechanical Engineering”
I want to create another metric with the data type “Sector Category” and the only dimension is Parent Company, so that I can use this to map all my per Parent Company revenue, to their corresponding Sector Category, or a column in the parent company dimension to that Sector Category.
How can I do this? I essentially want to move a dimension from being a dimension within the metric, to it being the return type of the metric (when the boolean is true).
Thanks!
Page 1 / 1
Hey Naomi,
Have you tried something like IF('XX_01c_Sector', 'Sector Category'))REMOVE FIRSTNONBLANK: 'Sector Category']? In this formula, you replace the boolean with the item from the dimension Sector Category, then select the first one for each Parent Company.
Obviously it mostly makes sense if you only have one Sector per Company, otherwise you should either filter more beforehand, or find another aggregation type (e.g. LASTNONBLANK, or LASTNONBLANK ON 'Sector Category'.'Importance Order'])
Hope this helps!
Hey Francois,
Thanks for your assistance!
The reason for creating the Boolean is I wanted a logic behind which Sector to select. We often have multiple sectors per parent company, so in previous steps, I created a count of each sector per parent, and then created the boolean to identify the sector with the highest count. This way I get one sector per parent, based on the highest count. Last/first nonblank is just based on the order of sectors within the dimension list, so wouldn’t allow that logic.
I managed to solve my problem another way. It’s an interesting workaround I think so maybe worth adding to the community:
Step 1 - create a boolean metric which can be based on any logic (in this case a max count) Step 2 - create a helper metric with both dimensions (Parent Company and Sector) with the data type dimension that I want as a result (Sector) -- so all possible parent / sector combinations and results. Step 3 - created a final metric where I used the Boolean metric from step 1 as a FILTER on the metric with all possible combinations
