Forum Discussion
Dynamic dimention values on chart
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
- 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. - 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)
- 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
- Lokisame6 years agoHelper I
That is not exacly what I had in mind. But thank you very much - that was helpful.