Moving the cell value in Matrix visual

HI sureshg2498 ,

Since the custom index is a calculated column, it is not possible to recalculate its order when filtering by region or using any slicer in your report. This happens because calculated columns do not update dynamically based on applied filters.

If the index were a measure, it could be recalculated dynamically, but the issue is that measures cannot be placed in the row fields of a matrix.

Alternative Solution

One way to work around this limitation is by creating a rank for each region. However, this approach increases the size of the data model, so it is not recommended.

Here’s the process:

1. Create a ranking column for each region.
2. Create a field parameter based on these ranking columns.
3. Establish a relationship between the parameter table and the main table (Sheet1).
4. Use the parameter column in the matrix row fields and as a slicer in the report.

This will allow the filters to be applied correctly and display the expected results.

Example DAX Code for Ranking by Region

The following DAX code creates a ranking for the East region, filtering only the data for that region when calculating the rank, repeat this for others:

East = 
VAR CurrentQuarter =Sheet1[Fiscal Quarter]
VAR SortedTable =
    FILTER (
       Sheet1,
       Sheet1[Fiscal Quarter] = CurrentQuarter && Sheet1[Region] = "East"

    )
RETURN
    RANKX((SortedTable),Sheet1[Index], , ASC,Dense)

 

The output expected:

 

Important Note

This is not best practice because the ranking columns are not created dynamically. This means that if new regions are added in the future, you will need to manually update the parameter table and create new columns for the additional regions.

Additionally, if you are working with large datasets, this approach can significantly increase the size of your model.