Forum Discussion

Lokisame's avatar
Lokisame
Helper I
6 years ago

Dynamic dimention values on chart

 

Hello,

I need to put the seller ranking on the chart. However, on the Y axis I would like to display a place in the ranking instead of First Name (it must be hidden), and First Name should be visible only for one selected seller. His position on the chart will change depending on the filters used.

Of course, in practice the problem is much more complex.

Suppose I have 2 tables:

 

I create the following chart:

 

However, I would like 1-John-3 to be displayed instead of names like this:

John can be in any position depending on the filtering.

How to put the values ​​from the ranking on the chart axis (Ranking = RANKX (ALLSELECTED (Table2 [Name]); [Value] ;; asc; Dense))?

I can't create separate tables with rankings for all variants because there is just too much of it.

 

Please help

 

 
Need Help
Tips and Tricks
Tutorial Requests

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    6 years ago

    Hello Lokisame 

     

    I have put a sample PBIX here.

     

    One method I can think of involves creating a special dimension table that contains all Names and possible Rank values.

     

    In my sample model I created these tables:

     

    NameRank

    (NameRank column is used on the category axis of the chart)

    RankNameNameRank
     GeorgeGeorge
     JamesJames
     JohnJohn
    1 1
    2 2
    3 3

     

    Name

    (to select which names will be displayed rather than ranks)

    Name
    George
    James
    John

     

    Data

    (sample data table you posted)

    NameValue
    George20
    James5
    John10

     

    In my sample model these tables are all disconnected.

     

    Then create this measure:

     

     

    Value by NameRank = 
// The selections of 'Name'[Name] will be displayed as Name rather than Rank
VAR SelectedNames = 
    VALUES ( 'Name'[Name] )
VAR AllselectedNames =
    CALCULATETABLE ( VALUES ( Data[Name] ), ALLSELECTED () )
VAR NamesWithRanks =
    ADDCOLUMNS(
        AllselectedNames,
        "RankIfRequired",
        IF (
            NOT CONTAINS ( SelectedNames, 'Name'[Name], Data[Name]),
            RANKX ( AllselectedNames, CALCULATE ( SUM ( Data[Value] ) ),,DESC )
        )
    )
RETURN
    SUMX (
        NameRank,
        VAR CurrentName = NameRank[Name]
        VAR CurrentRank = NameRank[Rank]
        RETURN
            CALCULATE (
            	SUM ( Data[Value] ),
            	FILTER (
            		NamesWithRanks,
                    // Display value against name if required
            		ISBLANK( [RankIfRequired] ) && Data[Name] = CurrentName
            		// Or display value against rank if required
                    || [RankIfRequired] = CurrentRank && NOT ISBLANK ( CurrentRank )
            	)
           )
    )

     

     

    This measure could probably be simplified a bit.

     

    The basic logic is to

    1. Get a list of selected Names from the Name table. These names are to be displayed as Names not ranks. (SelectedNames)
      You could have another method of choosing which Names are to be displayed.
    2. Assign a rank to each Name in the Data table, as long as that name has not been selected above, in which case leave the rank blank. (NamesWithRanks)
    3. Iterate over the NameRank table and return the desired value either against the row containing the Rank or the Name. I used SUMX so that the total will make sense if displayed - maybe not needed.

     

    Then a visual showing [Value by NameRank] by NameRank[NameRank] should look as intended:

     

    Hopefully that's of some use and can be adapted if needed. 

     

    Kind regards,

    Owen

    • Lokisame's avatar
      Lokisame
      Helper I
      6 years ago

      That is not exacly what I had in mind. But thank you very much - that was helpful.