Adhoc table, default dimension and measures

[shirleyc40]

Hi,

I have an adhoc table where users can choose their own dimensions and measure. Each dimension and measure has a show if condition where I find the substring count of the get field selections.

However, I don't want to put a data handling condition on the table where it wouldn't show anything if nothing was selected. Is there a way where if nothing was selected, it would show a default dimension and measure?

[Primrose]

Hi @shirleyc40,

Yes. You can change the conditional expression of the desired dimension/measure.

• If the dimensions are in one field (e.g. SelectableDimensions) and the users select values from that field, you can change the expression to If(IsNull(GetFieldSelections(SelectableDimensions)),[Dimension name], [the current expresion you have]).
• Otherwise, you could use GetCurrentSelections(), but you need to test if this is a suitable option for your case. I would prefer the first one.

Hope this helps.

[shirleyc40]

So if I wanted to show branch and total sales as the default dimension and measure, for branch I would put the show if expression to if(IsNull(GetFieldSelections('_Dimesnions')), Branch, SubstringCount(GetFieldSelections('_Dimensions'), 'Branch')) and similarly for the sales? Do I need to change the conditions of the other dimensions and measures? Does it matter that my table is hidden? 

Thank you

[Primrose]

Yes, the expression should look like this. You don't need to adjust the other expressions. 

What do you mean by "my table is hidden"? Do you have a show/hide condition for the object?

[shirleyc40]

I load an inline dimensions and measures table that I tag as hidden so that the selections won't affect the rest of the sheet. Also, the expression doesn't seem to work for me because without the data condition on the whole table, every dimension and measure just shows.

[Primrose]

Hi @shirleyc40, sorry for the delayed response.

You can add the condition to each dimension & measure in the table, not for the whole dimension. For example, your expression for Branch could be something like that:

If(Not IsNull(GetFieldSelections([_Dimensions],',',15)) AND WildMatch(GetFieldSelections([_Dimensions],',',15),'*Branch*')>0 ,1,0)

The '15' in the GetFieldSelections stands for 15 listed items. So if you have a list of 15, it's fine, otherwise, you may want to increase that number. 

If you always want to have one dimension and one measure selected, you don't need to have a calculated condition for the object. You may use something like 

If(IsNull(GetFieldSelections([_Dimensions], 1, If(Not IsNull(GetFieldSelections([_Dimensions],',',15)) AND WildMatch(GetFieldSelections([_Dimensions],',',15),'*Branch*')>0 ,1,0) )

for the dimension & measure you want to be selected if nothing else is selected.

Let me know if this helps.