Pick and Match

[MK_QSL]

I have enclosed sales data and want Type in Straight Table based on CustType Selection.

Please note that I can't link CustType with Type.. Please don't ask why !!

Let me know how can i achieve using PICK and MATCH function?

[jerem1234]

Don't need pick,match. You can use the p() function in set analysis:

SUM({<Type=p(CustType)>}Sales)

Please find attached.

Hope this helps!

[MK_QSL]

Yes but I want to show Type as a dimension based on CustType selection

[Peter_Cammaert · Accepted solution]

Something like this? I'm not using either Match() or Pick().

Peter

[jerem1234]

In my example, Type is a dimension and is being affected by selections in CustType. Just make selections in CustType and it will affect the table. Can you explain a little more about the behavior you're looking for if this isn't what you were looking for?

[MK_QSL]

Looks OK for me... Can you explain the function you used?

[jerem1234]

If you don't want the table to show anything if nothing is selected in CustType, then use:

if(GetSelectedCount(CustType)>0,SUM({<Type=p(CustType)>}Sales))

[Peter_Cammaert]

GetFieldSelections() gets the currently selected values from a specific field, as one string with each value separated by the string passed as second parameter. E.g. if you select Small, Very Small and Big in your CustType list,

= GetFieldSelections(CustType, ';')

will return the string 'Big;Small;Very Small'. Index() tries to find each and every dimension value in this string, and will suppress all other rows if you use it in a Calculated Dimension because of the IF() returning NULL().

The stuff with the leading and trailing semicolon is needed because selecting CustTyp = 'Very Small' would otherwise match 'Very Small' AND 'Small' which is not what you want. Therefor I'm forcing delimiters at the beginning and end of every value.

Note that GetFieldSelections() will return another, compressed value whenever the number of selected values is higher than some limit (default = 6) You can avoid this by including a third parameter with an unreasonably high value.

Good luck,

Peter