Forum Discussion
column based on selection
Hello Nobie,
I do not know any way of setting the "expand all down" state as a default for the visual, however I did found a workaround
What we are gonna be doing is changing from one page to another, one page will have this visual in "state 1" and the other on "state 2". For this to work seamlesly the visuals must have the same positioning in the canvas on both pages
1. Create a new table that has only one column with the names our pages will have
2. Create a slicer with this field containing the name of the pages and add a button to apply the selection
3. Diplicate the pages and add to each in the filter panel, the field we were using to change the columns in the visual,selecting one state for one page, and the remaining for the other one
4. Now we assign the action to our "apply" button. Choose the option "Page Navigation" and under the conditional format option, choose the field we created on the first step containing the name of our pages.
5. Now, we can select the page we want to go to, and click (Ctrl + click in PowerBI desktop) on the apply button and it will take us to the page with the other visual.
Extras:
1. This is the only way I was able to find using a Slicer, you can do it using bookmarks but to use them you will have buttons on the page.2. Hide one of the pages so its not accesible by users, this way we avoid confusion with the duplicate.
I hope this works for you! Its a bulky solution but it does the job
Hello Nobie !
You can achieve this using field parameters (https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters), after creating the parameters you will see this creates a new table with the following structure:
1. Create the parameter
FieldParameter =
{
("Category", NAMEOF('Table'[Category]), 0),
("Subcategory", NAMEOF('Table'[Subcategory]), 1),
("Manufacturer", NAMEOF('Table'[Manufacturer]), 2),
("ProductName", NAMEOF('Table'[ProductName]), 3)
}
The table would look something like this
Luckily we can edit this code to group one or more fields by adding another index before the line break as follows:
2. Add column to be used as filter
FieldParameter =
{
("Category", NAMEOF('Table'[Category]), 0, 1),
("Subcategory", NAMEOF('Table'[Subcategory]), 1, 1),
("Manufacturer", NAMEOF('Table'[Manufacturer]), 2, 2),
("ProductName", NAMEOF('Table'[ProductName]), 3, 2)
}
After creating the column it should look like this:
3. Use the created field in the visual
4. Create the filter with the column we added to the parameter
And, that would be all! Ill leave a Drive link for the report demo I took the photos of so you can download it
Demo report: https://drive.google.com/file/d/1bmOhSXTF-Ixne5yfMcirzFifUpbsAB3v/view?usp=sharing
I hope this helps!! If it did, mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!! Happy coding!
Hi, Thanks for the awesome solution .
But there is one issue ,with current solution provided if I select 1 or 2 then I have to drill down and the current stats of drill is not being saved.how can we achieve?
. I want something like if I select 1 visual should be automatically drill down to Category and subcategory and if select 2 visual should be drill down to manuacturer.
I was thinking like any function or something where I can use the table selection value.
Functions 1 like, if userselected =1 then category elseif userselected=2 thenManufacturer.
functions 2 like, if userselected =1 then subcategory elseif userselected=2 then Product.
so selecting function 1 will provide category and subcat
function 2 will return manufacturer and product.
and to use the function 1 and 2 in columns of visual.