Forum Discussion

Millabu's avatar
Millabu
Frequent Visitor
7 months ago
Solved

Field parameter slicer to add extra columns in table visual

I know there is a way to do this with field parameter, but after searching online and trying out myself with Power BI, I couldn't seem to find a solution that works how I wanted.. 

So, I have a table visual in my report which has multiple columns (business name, id, data source, number of leads, number of opportunities etc..). The users want to be able to show/hide 2 additional columns in the table: customer category and customer status. So the idea is that the other columns are always showing on the table and these 2 additional columns can be selected or unselected from a slicer, based on whether the user wants to view them in the table visual or not. So the slicer should only have the "customer category" and "customer status" showing. 

 

Additionally, I want these 2 extra columns to show on specific places in the table, and not showing as last columns when being selected. 

Thank you for any good tips! 

  • If the goal is to show or hide two specific columns using a slicer, there’s no need to add everything to the parameter table. Add the columns or measures for those two columns first, then add the parameter, followed by the remaining columns or measures. Any columns or measures not coming from the parameter table will not be affected by the slicer selection.

9 Replies

  • Additionally, I want these 2 extra columns to show on specific places in the table, 

    You can only show them before or after the Field Parameter block.  You cannot mix the two sections.

  • Hi Millabu 

    This isn’t currently possible, as lbendlin  mentioned. Field parameters can only be made visible within their own blocks and cannot be split. Using parameter views may help instead—you can add an extra column to the parameter table to categorize the fields, and a single field can belong to multiple categories.

     

  • Hi Millabu ,
    As the others mentioned this is not possible out of the box... buuuuuuut you can kind of hack it together. It still won't be the exact solution you need as this requires two slicers (one for each column you want to show/hide in custom positions in the table - thus not very scalable) but it may still work for your current project. Here are the steps:

    1. Add a blank column (I called it "blank") to your table. You can do it in Power Query or via DAX calculated column.
    2. Create a field parameter that has "blank" and "customer category" columns. When prompted, add a slicer to the page.
    3. In the dax code of the parameter table replace the "blank" with " " (This makes it easier to completely hide the column when customer category is not shown.
    4. In the slicer settings, force single select. Now you can choose whether to show the blank column or the customer category.
    5. Add a text box and overlay it on top of the slicer such that the radio buttons are not blocked, but the slicer values are (you can also set the slicer value font color to match your background, making it invisible. In the textbox you can write your own text, for example "Show" and "Hide". "Hide" should be positioned in line with the radio button that shows the blank column, and "Show" with the button that shows the customer category. Format your slicer header/title sensibly so it is clear that this controls the customer category. You can group the slicer and the text box together by selecting both and pressing ctrl+g, and verify in the selection pane that the text box is above the slicer. Here is how it looks: 
    6. Repeat steps 2-5 with "customer status". You can use the same blank column you used before, no need to make a second one.
    7. Now you should have: 2 field parameters, each with 2 fields (blank and either customer category or customer status) + 2 slicers formatted
    8. Create your table visual by dragging in the original columns, as well as each of the field parameter blocks in the desired order. In my example below I put customer category after the data source, and customer status after the leads:
    9. When the columns are hidden you will see the blank column, you can resize this manually to remove the visual gap it creates between columns
    10. Show-hide your two columns as desired:

    Not the cleanest and I could not figure out a way to make it work with just one slicer, but it does mostly what you want. Let me know if something is not clear or if you run into any issues.

  • Millabu's avatar
    Millabu
    Frequent Visitor

    Thank you all dk_dk , lbendlin and danextian for your replies so far! 

    So what I am trying to re-create is this that I know has been done before: 

    Here is the Field Parameter that was created:

    Parameter = {
        ("Account name", NAMEOF('Account_combined (2)'[name]), 0),
        ("Business ID", NAMEOF('Account_combined (2)'[VAT]), 1),
        ("Customer Lifecycle Status", NAMEOF('Account_combined (2)'[Lifecycle and category.Customer Lifecycle Status]), 2),
        ("Customer Category", NAMEOF('Account_combined (2)'[Lifecycle and category.Customer Categorization]), 3),
        ("Data in Source 1", NAMEOF('Account_combined'[Data in Source 1]), 4),
        ("Data in Source 2", NAMEOF('Account_combined'[Data in Source 2]), 5),
        ("CCM Leads", NAMEOF('Lead_combined'[CCM Leads (Filtered)]), 6),
        ("H&C Leads", NAMEOF('Lead_combined'[H&C Leads (Filtered)]), 7),
        ("Hydrogen Leads", NAMEOF('Lead_combined'[Hydrogen Leads (Filtered)]), 8),
        ("Nuclear Services Leads", NAMEOF('Lead_combined'[Nuclear Services Leads (Filtered)]), 9),
        ("CCM Opportunities", NAMEOF('Opportunity_combined'[CCM Opportunities (Filtered)]), 10),
        ("H&C Opportunities", NAMEOF('Opportunity_combined'[H&C Opportunities (Filtered)]), 11),
        ("Hydrogen Opportunities", NAMEOF('Opportunity_combined'[Hydrogen Opportunities (Filtered)]), 12),
        ("Nuclear Services Opportunities", NAMEOF('Opportunity_combined'[Nuclear Services Opportunities (Filtered)]), 13),
        ("Sales Activity in Number of Units", NAMEOF('Account_combined'[Sales Activity in Number of Units]), 14)
    }

     

     

    And there is a slicer visual and a table visual in the report, where looking at the Build section the slicer uses the Parameter 3 as value and table uses the same Parameter 3 in columns. 

     

     

     

     

     

    I see from the Slicer's visual Filters that it has only Customer Lifecycle Status and Customer Category ticked:


    And when there are no selections on the slicer, the table's visual Filters looks like this (both Customer Lifecycle Status and Customer Category are ticked but have "0" next to them): 

     

    And when one or both of the slicer values are selected, they show similarly as the rest of the columns:

     


    For some reason I have not been able to re-create this one, but it seems to be possible. So I don't know if I'm just missing some detail...

    • dk_dk's avatar
      dk_dk
      Icon for Super User rankSuper User

      Hi Millabu 
      You threw me a curve ball with this but (I think) i managed to replicate this behavior. Kind of. I can't get it to work with the classic slicer, but I can with the new one (but it looks different):

      I added the parameter to the new slicer (you can format it to a vertical list but it wont have the selection boxes, rather its a bunch of cards. I selected by hand ALL of the parameters. The order in which they were selected decides the column order. I duplicated the slicer. Only on the second slicer  I added a visual level filter to filter it to the category and lifecycle columns. Then I simply hid the first slicer (View>Selection>Hide visual) and this is what I was left with:

      It has the behavior you want but I cannot format it the same way.

      Could you check:
      - if this works for you for your recreation
      - which type of slicer visual is used in the original project you took the screenshots of
      - if there is a hidden slicer on the report page in the original project as well


       

      • Millabu's avatar
        Millabu
        Frequent Visitor

        Hello, and thank you so much for puttig effort on solving this! You asked: 

        - which type of slicer visual is used in the original project you took the screenshots of --> List slicer (new slicer)
        - if there is a hidden slicer on the report page in the original project as well --> there are no hidden slicers 


         

        I tried to replicate what you did, and maybe I'm just not seeing something or then there is another reason my table visual is not behaving same way as yours:

         

        1. I added a slicer (List slicer) and a table visual on a report page. I put the Parameter in the table visual values, and after that I selected ALL the values in the slicer. 

        2. I duplicated the slicer and I added a visual level filter to filter it to the category and lifecycle columns. And then I hid the first sicer. 

        3. Left with this, I havea table with all the columns, and when I choose a value or boht values on the slicer, the table only filters to those ones and does not show the rest of the columns. Below are the screenshots to demonstrate this. 

         

         

         

         

         


        I don't understand what I'm missing here 😅

    • danextian's avatar
      danextian
      Icon for Super User rankSuper User

      If the goal is to show or hide two specific columns using a slicer, there’s no need to add everything to the parameter table. Add the columns or measures for those two columns first, then add the parameter, followed by the remaining columns or measures. Any columns or measures not coming from the parameter table will not be affected by the slicer selection.

  • Hi Millabu,

    Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to danextiandk_dklbendlin,  for those inputs on this thread.

    Has your issue been resolved? If the response provided by the community member danextiandk_dklbendlin,  addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

    Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

    Thank you for using the Microsoft Community Forum.

    • v-kpoloju-msft's avatar
      v-kpoloju-msft
      Icon for Community Support rankCommunity Support

      Hi Millabu,

      Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

      Thank you.