Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have to get columns based on selection .
I have created a table with values 1 and 2 and added in slicer.
now I want to create two different measure/paramter/calculated column whaterver u call, let name it 2 function.
if I select 1 , the first function should return product category column and 2 function to return product sub category
if I select 2, the first function shlould return manufacturrer column and if 2 selected then return product name.
My motto is to get two get two different column based on single selection.
like 1 selected getting below
when 2 selected getting the below result
Please help
Solved! Go to Solution.
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
In that case being the waterfall visual, you can build the visual without using the field parameter keeping the page navigation scheme, just add both fields to the x-axis. In one page use Category and Subcategory, and in the other Manufacturer and Product name
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
Hi Daniel,
I got what u are trying to say.
U r truly a genius.
I got one issue just ,please help.
The field parameter technique is not helping in case of waterfall chart as in waterfall chart can be drill down if I add a column in breakdown option.
But using the above solution I am not able to achieve . If some work around possible please let me know.
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |