Dynamic Selection and display of Fields Based on Category

[RKM]

We were recently exploring Field Parameter (Preview). This is nice. But something that our client requested is, following (... and seems Field Param is not capable to address it!)

So, we are having a table [TABLE_FIELDMAP] with columns like below, where there is a mapping between which column is relevent to Which category

SPORT	FIELD
CRICKET	RUN, WICKET, CATCH, TROPHY_WON, MAJOR_ACHIEVEMENT
FOOTBALL	GOALS, TROPHY_WON, MAJOR_ACHIEVEMENT

Next we have [TABLE_RECORDBOOK] with following 

PLAYER	YEAR	SPORT	RUN	WICKET	CATCH	GOALS	TROPHY_WON	MAJOR_ACHIEVEMENT
SAM	2021	CRICKET	810	11	6	NULL	2	Orange Cap
SAM	2022	CRICKET	667	4	8	NULL	0	NULL
ROB	2022	FOOTBALL	NULL	NULL	NULL	9	2	NULL
STEVE	2021	CRICKET	256	0	7	NULL	0	Young Talent

The requirement is: 

There should be a slicer called [Sport] which will show all available Sport(s). Upon selecting a value from that Slicer, there should be a table/matrix where, we'd see  [Player], [Year], [Sport] AND Only those column(s) which are applicable to that sport. i.e. when Foolball is selected - then only [Goals] & [Trophy_Won] should be visible (and not Run/ Catch/ Wicket - etc.)

~ by the way, obviously above two table(s) are here for illustration only. In reality both having a lot of rows and categories. So... a solution with [hard coded column names] etc. not possible.

Has anyone addressed such issues please? If so, kindly share some clue. Thanks a lot in advance.

[Ashish_Mathur]

Hi,

Considering in the second table there will be a number only under the relevant column, I do not see the relevance of the first table.  In the Query Editor, you should select the first 3 columns, right click and select "Unpivot Other Columns".  Create a slicer of Sport and select Football.  Create a matrix visual with Player, Year in the row labels and Attribute in the Column labels.  Write this measure

Total = sum(Data[Value])

Hope this helps.

[RKM]

Thumbs Up!

Thanks @Ashish_Mathur . Your solution is almost what we need. But a few additional elements are there, where I believe my question (or the example) didn't clarify fully, so I apologies for that.

1. Firstly, No.. its not necessary to have only Int in all columns.
2. Also, there could be fields applicable to certain sport but may, remain null for ceratin player/certain year.

SPORT	FIELD
CRICKET	RUN, WICKET, CATCH, TROPHY_WON, MAJOR_ACHIEVEMENT
FOOTBALL	GOALS, TROPHY_WON, MAJOR_ACHIEVEMENT

PLAYER	YEAR	SPROT	RUN	WICKET	CATCH	GOALS	TROPHY_WON	MAJOR_ACHIEVEMENT
SAM	2021	CRICKET	810	11	6	NULL	2	Orange Cap
SAM	2022	CRICKET	667	4	8	NULL	0	NULL
ROB	2022	FOOTBALL	NULL	NULL	NULL	9	2	NULL
STEVE	2021	CRICKET	256	0	7	NULL	0	Young Talent

~ Again I apologies, because I am trying hard to give a good example and I guess this above one could be considered better. The whole point is - Table 1 Should drive which column to display for which sport from Table 2.

Its NOTthe text/numeric /Null data points in Record table to decide which column to display.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[lbendlin]

 a solution with [hard coded column names] etc. not possible.

Then what do you call your reference table with sports and columns?  That's not hardcoded?

Field parameters can achieve the goal you state.  You  should also consider enabling "Peronalize Visuals" and teach your users how to use that feature. It does the sameas Field Parameters, and then some.