Field Parameters Order in a Matrix

[elia22gr]

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

[Nigel · Accepted solution]

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

[elia22gr]

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

[elia22gr]

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.

[Fowmy]

@elia22gr 

Please check if the sorting has anything to with this behavior.

[elia22gr]

@Fowmy The matrix sorting or the columns sorting in Field Parameters table?

[elia22gr]

The same behaviour is both in desktop and Service.

[elia22gr]

@Fowmy I tried, but no luck! It was working properly but now is not without making any change in the report

[Fowmy]

@elia22gr 

Could you share the file with Dummy data replicating the scenario?

[Fowmy]

@elia22gr 
That's strange, can you remove the Parameter from the visual and re-assign?