Forum Discussion

sureshg2498's avatar
sureshg2498
Advocate II
1 year ago
Solved

Matrix Visual cell value moving to front

Hi All,

 

We have a data table as shown below.

Raw Data- Table Name: Customers

IndexCountryFiscal QuarterCustomer NameHealth
1USAFY Q1 2024T Mobile Red
2USAFY Q1 2024J&J Green
3USAFY Q2 2024WallmartGreen

 

Created the Matrix visual with the Health slicer as shown below.

 

DAX for "Custom Index" column:

Custom Index =
VAR CurrentCountry = 'Customers'[Country]
VAR CurrentQuarter = 'Customers'[Fiscal Quarter]
VAR CurrentIndex = 'Customers'[Index]
VAR SortedTable =
    FILTER (
        'Customers',
        'Customers'[Country] = CurrentCountry &&
        'Customers'[Fiscal Quarter] = CurrentQuarter
    )
RETURN
    RANKX(SortedTable, 'Customers'[Index], , ASC, Dense)

 

 

When I select the Slicer value 'Green' the report will display as shown below.

 

But our expected results would be as shown below. In a single row.

 

 

Would it be possible to get the visual as shown above.

Could anyone please help me on this?

 

 

Thanks,

Suresh.

 

Bibiano_Geraldo rajendraongole1 Ritaf1983 danextian

 

Need Help
  • SShaikh's avatar
    SShaikh
    1 year ago

    Here is the sample .pbix file, use the link to see the solution:
    TestFile.pbix


    Output:

    Tips:
    In third matrix table added custom index column and make the matrix layout tabular

    Rename the custom index colum with blank character

    make the +/- icon option off

    and reduce the width of the column to make the value diappear

    hope this trick will solve your problem.

13 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Only if you remove the "Custom Index"  field from the rows, and modify your measure.

    • sureshg2498's avatar
      sureshg2498
      Advocate II
      1 year ago

      Thank you so much Ibendlin fo your reply.

      Could you please let me know what chnges needs to be done to the measure ?

      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago

        Implicitly via First Customer and First Health  (taking advantage of the fact that Green sorts before Red)

         

         

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    Hi sureshg2498 ,
    In your Rankx, just rank by health as shown bellow:

    Custom Index = 
VAR CurrentCountry = 'Customers'[Country]
VAR CurrentQuarter = 'Customers'[Fiscal Quarter]
VAR CurrentIndex = 'Customers'[Index]
VAR SortedTable =
    FILTER (
        'Customers',
        'Customers'[Country] = CurrentCountry &&
        'Customers'[Fiscal Quarter] = CurrentQuarter
    )
RETURN
    RANKX(SortedTable, 'Customers'[Health], , ASC, Dense)


    By default, your Matrix will look like this:

     

    • sureshg2498's avatar
      sureshg2498
      Advocate II
      1 year ago

      Thank you so much for your reply, Bibiano.

      I did exactly what you said. But i see only one row. Could you please help me ?

       

      Thanks,

      Suresh.

       

      • Bibiano_Geraldo's avatar
        Bibiano_Geraldo
        Super User
        1 year ago

        To be more accurate, please can you share a sample file? Make sure to not share sensitive information.

         

        I didn't saw your file here on the post