Union of custom columns (Static Values)

Ananth_Periyasamy
1 mth ago
8replies
Answered

I am in need of creating a static dimension with multiple measure names such as: sales, expenses, cost, count, etc. These are to be given to a multiselect dropdown in a report that gives the measure names in multiple rows as per selection. I have tried creating a list of custom columns using the Union and facing the error

"Lists must use members from the same table and column"

Is there any possibility of executing this?

Thanks in advance

Ananth

- Lead Consultant Advanced Analytics
- Michael_Daun
- 1 mth ago
- Reported - view
Hi Ananth,
this sounds as if you are looking for a Parameters! This is an option when opening Formulate...

At Step 1 select "Global" (i.e. the values do not come from your model! I think you only need the names of measures, not the measures itself!), at Step 2 select "Text" as List Type and create your list by simply adding caption-value-pairs.

Finally, Step 3, select the type of slicer/filter which should visualize your parameters.

When adding this parameter to your chart the user can select a measure name from the drop down. Subsequently, the corresponding measure is assigned to a "dynamic measure placeholder" - technically a formula - which finally delivers the values of the selecred measure.

I hope that was your requirement at all! Have a look at this article which describes the interplay between a parameter and dynamic measures or dynamic dimensions: https://community.pyramidanalytics.com/t/p8y69k0/tip-of-the-day-dynamic-dimensions-and-measures

Best regards,
Michael
- Ananth_Periyasamy
  
  1 mth ago
  
  Reported - view
  Thank you for your response.
  
  I however wanted to try a multiselect measure by creating a dimension and converting to list as it provided data driven multiselect option.
  
  Regards,
  
  Ananth

VP Product Management
Ian_Macdonald
1 mth ago
Reported - view

Are you requiring the selection of groups of measures with a single click?

For example with one click put all the outgoing Measures ,Expense, cost, Overhead, on the rows, or with another click, Sales, Margin and Net Margin and Units sold on the rows?

Ian

Ananth_Periyasamy
1 mth ago
Reported - view

Hi ,

I needed multi select on measure in a dropdown that needs to be append in the rows. For example, if I select the Sales, Expense, Cost from multi select dropdown the report shows as 3 rows with each measures named with corresponding selection.

Expected Output:

Measures	A	B	C
Sales	x	x	x
Expense	x	x	x
Cost	x	x	x

Example for Multi Select dropdown:

Regards,

Ananth

- VP Product Management
- Ian_Macdonald
- 3 wk ago
- Reported - view
Hi

Measures in a slicer and Measures based Parameters do not support Multi Select as an option.

I tried a number of approaches as a workaround but hit roadblocks in each one. If I ever overcome them, I'll come back and let you know.

For now, I'm afraid you're stuck.

Hope that helps.

Ian
- Michael_Raam.1
- 3 wk ago
- Reported - view
Hi,

Although true multi selections on measures is not supported.

If you need a dim named "Measures" , you could consider to create it via a file ( Excel , CSV ) and uploaded it to the model.

I would preferer to create it as a table / view in the data source.

Regrading the multi selection on "Measures", if "Measures" is a dim you could link it to the tables in the model
or create a calculation with a case /if expression relative to the "Measures". currentmember.

This will give the user experience ( or simulation ) of a multi selection on "Measures".

Tnx
- Ananth_Periyasamy
- 2 wk ago
- Reported - view
Thank you!
- VP Product Management
- Ian_Macdonald
- yesterday
- Reported - view
Hi

I have a workaround for this, although it does require the forthcoming release, 2025.01, which supports the StrtoSet() function for Measures. Prior to 2025.01, StrToSet() only supports non Measure hierarchies.

Choose a hierarchy that either isn't used much or has few Members. In my case, I'm using the Channel hierarchy in the demo Sales model.

Create dummy custom members in this hierarchy with the same name as the Measures you want to use:

Note that the custom member I saved into the Channel hierarchy. Duplicate this member as many times as you need for the Measures you want to use and rename the duplicates to be the same as your Measure names. See the tabs at the bottom on the screen shot above.

Create a muti select parameter against the Channel hierarchy and add the dummy Measure custom members:

Create a Custom List against the model and use the following code. The curly brackets around the Parameter makes it a list I can access using the GetElement() function to retrieve the individual items that have been selected. For each entry, I retrieve the caption for each element and concatenate any additional text required so that the string matches the name in the Measure dimension and add a comma between each element. Then the StrtoSet() function matches the string of Measure names to a set or list of Measures.

StrToSet(
[Measures],
"SalesData " + GetElement({[Channels].[Channel].@[1ce8f009-3b08-460e-b2ef-1d845539f973]},0).caption + "," +
"SalesData " + GetElement({[Channels].[Channel].@[1ce8f009-3b08-460e-b2ef-1d845539f973]},1).caption + "," +
"SalesData " + GetElement({[Channels].[Channel].@[1ce8f009-3b08-460e-b2ef-1d845539f973]},2).caption + "," +
"SalesData " + GetElement({[Channels].[Channel].@[1ce8f009-3b08-460e-b2ef-1d845539f973]},3).caption + "," +
"SalesData " + GetElement({[Channels].[Channel].@[1ce8f009-3b08-460e-b2ef-1d845539f973]},4).caption + "," +
"SalesData " + GetElement({[Channels].[Channel].@[1ce8f009-3b08-460e-b2ef-1d845539f973]},5).caption
)

Then put this Measure set on your rows. This will pull in the Parameter to make your selections:

You just now ned to wait for 2025.01 to be released and then lobby to get your Pyramid instance upgraded!

Hope that helps.

Ian