Dynamic dimention values on chart

[Lokisame]

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

[OwenAuger]

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)

Rank	Name	NameRank
	George	George
	James	James
	John	John
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)

Name	Value
George	20
James	5
John	10

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]

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