Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mim
Advocate V
Advocate V

Convert Scatter chart to Matrix Visual ( Show Ranking using a disconnected Dimension)

Hello

 

I have this Scatter charts,  unfortuately Scatter chart does no have conditional formating, so my best shot will be matrix visual, unfortunately, Matrix visual accept only measures in the value Area, any ideas how to approach this

 

the measure to rank was based on this solution

 

I have a working solution here using calculated columns, but if the user select two items in the slicer, it breaks, and there is no way to have a dynamic calculated column

 

so basically

 

Capture.PNG

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi there @mim

 

My suggestion is in this pbix, 2nd report tab.

 

  1. Create a disconnected rank table. I called this 'Rank Disconnected' with single column 'Rank Disconected'[RNK]. This column is placed on the matrix in the Rows well.
  2. Create a new measure to use in the matrix:
    Skyline Subsystem Owen =
    VAR CurrentRank =
        SELECTEDVALUE ( 'Rank Disconnected'[RNK] )
    VAR SubSystemCount =
        DISTINCTCOUNT ( Forecast[SUBSYSTEM] )
    RETURN
        IF (
            NOT ISBLANK ( CurrentRank ),
            IF (
                CurrentRank <= SubSystemCount,
                LASTNONBLANK (
                    TOPN ( CurrentRank, VALUES ( Forecast[SUBSYSTEM] ), Forecast[SUBSYSTEM], ASC ),
                    0
                )
            ),
            [Skyline subsystem Date]
        )

This measure is set up to behave similarly to your existing measure, but it uses LASTNONBLANK & TOPN to find the SUBSYSTEM with rank corresponding to the value of 'Rank Disconnected'[RNK].

 

Let me know if that helps 🙂

 

Best regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

thanks @OwenAuger  for this. That does help. In my case, what I was doing that I set up a calculated column for ranking in the Subsystems table (suggested on this link when I first asked the question: Solved: Re: How to display text values in matrix along tim... - Microsoft Power BI Community) I will see if I can extend that DAX column logic to include the SubSystem Date when rank is 0(this means less changes to live report) . Or else can follow your approach by testing in a copy and once successful, I can make it live. 

OwenAuger
Super User
Super User

Hi there @mim

 

My suggestion is in this pbix, 2nd report tab.

 

  1. Create a disconnected rank table. I called this 'Rank Disconnected' with single column 'Rank Disconected'[RNK]. This column is placed on the matrix in the Rows well.
  2. Create a new measure to use in the matrix:
    Skyline Subsystem Owen =
    VAR CurrentRank =
        SELECTEDVALUE ( 'Rank Disconnected'[RNK] )
    VAR SubSystemCount =
        DISTINCTCOUNT ( Forecast[SUBSYSTEM] )
    RETURN
        IF (
            NOT ISBLANK ( CurrentRank ),
            IF (
                CurrentRank <= SubSystemCount,
                LASTNONBLANK (
                    TOPN ( CurrentRank, VALUES ( Forecast[SUBSYSTEM] ), Forecast[SUBSYSTEM], ASC ),
                    0
                )
            ),
            [Skyline subsystem Date]
        )

This measure is set up to behave similarly to your existing measure, but it uses LASTNONBLANK & TOPN to find the SUBSYSTEM with rank corresponding to the value of 'Rank Disconnected'[RNK].

 

Let me know if that helps 🙂

 

Best regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

I tried since last year all kind of workaround, I should have just asked you in the first place:) I knew I had to break the relationship with the rank Table, but did not know how to have a measure to relate to.

 

that's a brilliant solution

@OwenAuger  is there a way to filter the table forecast, I am trying to have a tooltip reports to show the ITR by subsystem

 

I am using yur measure now, can you have a look here please when you have time

 

cheers

Mim

Hi @mim

 

No problem, I suspected you might need to do something like that 🙂

 

File uploaded here

https://www.dropbox.com/s/dhtcnai3btqoji2/SKYLINE%20Owen%20edit.pbix?dl=0

 

  1. Create a measure like this:
    Subsystem Matches Skyline = 
    VAR CurrentSkylineSubsystem =
        CALCULATE ( [Skyline Subsystem Owen], ALLSELECTED () )
    RETURN
        IF ( SELECTEDVALUE ( Forecast[SUBSYSTEM] ) = CurrentSkylineSubsystem, 1 )
  2. Add a Visual Level filter to the table on the Detail tab setting Subsystem Matches Skyline = 1
  3. Now the toolitp table corresonds to the SUBSYTEM you are hovering over.

 

Best regards,

Owen 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

Hi @OwenAuger I wish I had come across this before. I had to develop a few skylines, one of them was for Commissioning. I managed to achieve the general layout however what I cannot do is to show the Dates at the bottom of the matrix as I can see in your Power BI report. I tried matching the settings of the matrix visual from your report to mine but coulndn't find anything that will help. was hoping you could possibly help? Basically I need to show the Dates at the bottom. 

KD_PBI_DA100_0-1642581880130.png

 

Hi @Anonymous 

Glad this is still proving useful a couple of years on 🙂

I had to refresh my memory of what is going on here.

 

The main "trick" here is that the measure is set up to return the relevant date as the total, at the bottom of the matrix.

 

If you have a look at the Skyline Subsystem Owen measure, if CurrentRank is blank, it returns [Skyline subsystem Date]. This happens when there isn't a single rank in the filter context (i.e. current row of the matrix), which only happens on the "total"row.

 

Skyline Subsystem Owen = 
VAR CurrentRank = SELECTEDVALUE( 'Rank Disconnected'[RNK] )
VAR SubSystemCount = DISTINCTCOUNT ( Forecast[SUBSYSTEM] )
RETURN
IF (
    NOT ISBLANK ( CurrentRank ),
    IF (
        CurrentRank <= SubSystemCount,
        LASTNONBLANK (
            TOPN ( CurrentRank, VALUES ( Forecast[SUBSYSTEM] ),Forecast[SUBSYSTEM],ASC ),
            0
        )
    ),
    [Skyline subsystem Date]
)

 

You should also set the column headers to white text in order to hide them. It looks like this wasn't set up correctly on all visuals in the PBIX.

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi @OwenAuger 

 

Great contribution with the Skyline. I am one of the person who use it. I come here with a strange request, hope it is possible. 

 

In you DAX measure above, is it possible to sort those values based on a different column.

Ex: TOPN ( CurrentRank, VALUES ( Forecast[SUBSYSTEM] ),Forecast[SUBSYSTEM],ASC )

I do not want these values to be sorted by the Subsytem column, instead a different column from the table. I tried many ways, but could not figure it.

 

Thank you in advance,

Sri

@SriKandimalla apologies for the late reply - I somehow missed this earlier!

Have you solve it already?

It should certainly be possible. The 3rd argument of TOPN is the expression used to determine the ranking, so if you change it to an expression that returns the value from the other column, it should work fine.

 

e.g.

TOPN (
    CurrentRank,
    VALUES ( Forecast[SUBSYSTEM] ),
    CALCULATE ( SELECTEDVALUE ( SomeTable[Column] ),
    ASC
)

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.