Moving the cell value in Matrix visual

[sureshg2498]

Hi All,

We have the sample data set as shown below.

Index	Fiscal Quarter	Customer Name	Health	Region
1	FY Q1 2024	T Mobile	RED	South
2	FY Q1 2024	J&J	GREEN	Central
3	FY Q2 2024	Wallmart	GREEN	West
4	FY Q4 2024	SAP	YELLOW	East
5	FY Q1 2023	APPLE	RED	South
6	FY Q2 2023	Amazon	YELLOW	South
7	FY Q2 2023	NetFlex	GREEN	West
8	FY Q2 2023	Aha	RED	Central
9	FY Q2 2024	Samsung	YELLOW	West
10	FY Q2 2023	Moto	RED	West

I have created the Matrix visual as shown below.Conditional formatting is based on Health field.

Customer Name Conditional formatting is based on Health field.

DAX for "Custom Index" column

Custom Index =

VAR CurrentQuarter =Sheet1[Fiscal Quarter]

VAR SortedTable =

    FILTER (

       Sheet1,

       Sheet1[Fiscal Quarter] = CurrentQuarter

    )

RETURN

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

Issue:

When i select the Slicer Region as  "West" report displays the data as shown below.

But our expected results are as below.We do not need the empty cell.

When we uncheck the "West"  it should go to the default view.

Could anyone please help me on it.

Thanks,

Suresh.

@danextian  @lbendlin @rajendraongole1 @johnt75 @Ritaf1983 @Bibiano_Geraldo @DataNinja777 

[Bibiano_Geraldo]

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.

[sureshg2498]

Thank you so much Bibiano.Your insights were incredibly helpful. I really appreciate the time you took to guide me.

[lbendlin · Accepted solution]

No need for RANKX.

[sureshg2498]

Thank you so much IbendIin. Your assistance was incredibly helpful.Thank you for taking the time to help me solve that problem. Your expertise was invaluable. I wanted to take a moment to express my gratitude for your assistance with my issue. I really appreciate it. Thank you once again!