Matrix Visual cell value moving to front

[sureshg2498]

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	J&J	Green
3	USA	FY Q2 2024	Wallmart	Green

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

[Bibiano_Geraldo]

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]

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]

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]

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

Index	Country	Fiscal Quarter	Customer Name	Health
1	USA	FY Q1 2024	T Mobile	Green
2	USA	FY Q1 2024	J&J	Green
3	USA	FY Q2 2024	Wallmart	Red

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.

[SShaikh · Accepted solution]

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]

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

Index	Country	Fiscal Quarter	Customer Name	Health
1	USA	FY Q1 2024	T Mobile	Green
2	USA	FY Q1 2024	J&J	Green
3	USA	FY Q2 2024	Wallmart	Red

[SShaikh]

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)

[sureshg2498]

Thank you so much Shaikh. Much appreciated.

[Bibiano_Geraldo]

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. 

[lbendlin]

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

[sureshg2498]

Thank you so much Ibendlin fo your reply.

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

[lbendlin]

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

[sureshg2498]

Thank you for the reply Ibendlin.

I have removed the custom index from the visual.

However, I could not see the Red health record as shown below without any slicer value. selection.

I'm new to Power BI development. Would it be possible to attach the Pbix file for the reference.

Thanks,

Suresh.