Forum Discussion
Field Parameters Order in a Matrix
Hi all,
I have a matrix and I am using a field parameter to allow users make their own table. In the Field Parameters I have Columns from my Dimensions Tables (Products, Customers, SalesStructure)
From Products I have selected the ITEM Group, Product Category, Product Subcategory, Brand, Pack Size, Promo etc
From Customers I have the Channel, Chain, Customer
From Sales Structure I have the Manager, SalesPerson
In the Fields Parameters Table I have created a column just for grouping reasons to indicate the table (Products if the column is from Products tables, Customers and SalesStructure).
The problem that I am phasing now (it wasn't at the begging) is when I am selecting with this order
Product Category - Brand - Channel - Pack Size - Promo in my slicer the order in my matrix is different
Product Category - Brand - Pack Size - Channel - Promo.
Why this is happening now? It is a bug?
Thank you in advance,
Eleftheria
Hi elia22gr ,
I too have been suffering with this. My rationale for having the columns in the correct order is that I have 2 tables overlayed so that I can dynamically change the column headers. I need to be able to have columns in both tables in the order I need.
During this implementation, I found a workaround to the Columns in matrix not being sorted in the Field Parameter Sort Order.
The workaround is to create another table with the same number of rows as your Field Parameter table and just 2 columns.
Column 1 (Sort Order) must be an integer and will be the sort column (start at 0 like in the parameter table)
Column 2 (Label) will be what you want the users to select in a slicer.
In the model, create a 1-2-1 relationship between this newly created table and the Parameter Table on the Sort Order Columns.
Change the Field Parameter Slicer in your visual to use the Label column from the newly created table and not the Field Parameter value.
Leave the table visual as it is with the Field Parameter as the Values.
Now, when you select the columns from this new slicer, they appear in the matrix / Table in the correct order.
Regards
Nigel
9 Replies
Hi elia22gr ,
I too have been suffering with this. My rationale for having the columns in the correct order is that I have 2 tables overlayed so that I can dynamically change the column headers. I need to be able to have columns in both tables in the order I need.
During this implementation, I found a workaround to the Columns in matrix not being sorted in the Field Parameter Sort Order.
The workaround is to create another table with the same number of rows as your Field Parameter table and just 2 columns.
Column 1 (Sort Order) must be an integer and will be the sort column (start at 0 like in the parameter table)
Column 2 (Label) will be what you want the users to select in a slicer.
In the model, create a 1-2-1 relationship between this newly created table and the Parameter Table on the Sort Order Columns.
Change the Field Parameter Slicer in your visual to use the Label column from the newly created table and not the Field Parameter value.
Leave the table visual as it is with the Field Parameter as the Values.
Now, when you select the columns from this new slicer, they appear in the matrix / Table in the correct order.
Regards
Nigel
The same behaviour is both in desktop and Service.
This is what is coming from performance analyzer
VAR __DS0FilterTable0 =TREATAS({("PRODUCTS", "'DIM_Product'[ITEM_G]"),("PRODUCTS", "'DIM_Product'[PRODUCT CATEGORY]"),("PRODUCTS", "'DIM_Product'[BRAND]"),("PRODUCTS", "'DIM_Product'[PACK SIZE]"),("CHANNEL", "'DIM_Channel'[CHANNEL]"),("PRODUCTS", "'DIM_Product'[Promo Indic]")},'Table Rows'[Table],'Table Rows'[Table Rows Fields])Even though the selection order was DIM_Product'[ITEM_G] - DIM_Product'[PRODUCT CATEGORY] - DIM_Product'[BRAND] - DIM_Channel'[CHANNEL] - DIM_Product'[PACK SIZE] - DIM_Product'[Promo Indic]I tried making the selection slower but nothing changed.Something that I noticed is that if my selections are till the PACK SIZE everything works properly, but if I select anything after the PACK SIZE even if it not from product table the order suffled.
Correct Order: DIM_Product'[ITEM_G] - DIM_Product'[PRODUCT CATEGORY] - DIM_Product'[BRAND] - DIM_Channel'[CHANNEL] - DIM_Product'[PACK SIZE]
Problem: DIM_Product'[ITEM_G] - DIM_Product'[PRODUCT CATEGORY] - DIM_Product'[BRAND] - DIM_Channel'[CHANNEL] - DIM_Product'[PACK SIZE] - Anything
