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 Index Country Fiscal Quarter Customer Name Health 1 USA FY Q1 2024 T Mobile  Red 2 USA FY Q1 2024...
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.

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

sureshg2498's avatar
sureshg2498
Advocate II
1 year ago

Hi Bibiano - Thank you so much for your time.

Unfortunately, I do not have option to attach the pbix file. Below is the data that I have in the table.

Table: Customers

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

 

By Default the results:

 

For Green selection: Client is expecting the results as shown below.

 

For Red:

 

If you have the solution, kindly attach the pbix file.

 

Thank you so much for help and support. Much appreciated.

 

 

 

 

Thanks,

Suresh.

 

 

 

 

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    Hi, to share you need to upload the file to the cloud like onedrive or other, and share the link for download here in comments. 

  • SShaikh's avatar
    SShaikh
    Frequent Visitor
    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.

    • sureshg2498's avatar
      sureshg2498
      Advocate II
      1 year ago

      Thank you so much Shaikh. Much appreciated.

       

    • sureshg2498's avatar
      sureshg2498
      Advocate II
      1 year ago

      I sincerely apologise for disturbing you. The same logic/Trick is not working for the below dataset. Could you please help me on it.

       

      IndexCountryFiscal QuarterCustomer NameHealth
      1USAFY Q1 2024T Mobile Green
      2USAFY Q1 2024J&J Green
      3USAFY Q2 2024WallmartRed
      • SShaikh's avatar
        SShaikh
        Frequent Visitor
        1 year ago

        This file has data sets with different variations, hope this could help you to get desired result,

        TestFile2.pbix

        And you could play with the order in Rankx function for your desired output( change the order to DESC )

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

        Or order it based on Index and Health both the columns (below code needs a calculated column -

        Health Priority = IF('Data 1'[Health]="Green",1,2))

        ) 

        Custom Index 2 = 
VAR CurrentCountry = 'data 1'[Country]
VAR CurrentQuarter = 'data 1'[Fiscal Quarter]
VAR CurrentIndex = 'data 1'[Index]
VAR SortedTable =
    FILTER(
        'data 1',
        'data 1'[Country] = CurrentCountry &&
        'data 1'[Fiscal Quarter] = CurrentQuarter
    )
RETURN
     RANKX(SortedTable,'Data 1'[Health Priority] * 10000 + 'data 1'[Index], , ASC, Dense)