Transforming a data type Boolean into a Dimension

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

(see imgs)

Thanks again for your help.

Best,
Naomi