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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RKM
Helper IV
Helper IV

Dynamic Selection and display of Fields Based on Category

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

SPORTFIELD
CRICKETRUN, WICKET, CATCH, TROPHY_WON, MAJOR_ACHIEVEMENT
FOOTBALLGOALS, TROPHY_WON, MAJOR_ACHIEVEMENT

 

Next we have [TABLE_RECORDBOOK] with following 

 

PLAYERYEARSPORTRUNWICKETCATCHGOALSTROPHY_WONMAJOR_ACHIEVEMENT
SAM2021CRICKET810116NULL2Orange Cap
SAM2022CRICKET66748NULL0NULL
ROB2022FOOTBALLNULLNULLNULL92NULL
STEVE2021CRICKET25607NULL0Young 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.

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.
SPORTFIELD
CRICKETRUN, WICKET, CATCH, TROPHY_WON, MAJOR_ACHIEVEMENT
FOOTBALLGOALS, TROPHY_WON, MAJOR_ACHIEVEMENT

 

 

PLAYERYEARSPROTRUNWICKETCATCHGOALSTROPHY_WONMAJOR_ACHIEVEMENT
SAM2021CRICKET810116NULL2Orange Cap
SAM2022CRICKET66748NULL0NULL
ROB2022FOOTBALLNULLNULLNULL92NULL
STEVE2021CRICKET25607NULL0Young 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.

 

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

 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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors