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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nobie
Helper II
Helper II

column based on selection

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

Nobie_1-1682863584849.png

when 2 selected getting the below result

Nobie_2-1682863668210.png

 

Please help

 

 

 

1 ACCEPTED SOLUTION
danielbastidas
Regular Visitor

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

danielbastidas_0-1682982627892.png

 

2. Create a slicer with this field containing the name of the pages and add a button to apply the selection

 

danielbastidas_1-1682982729562.png

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

 

danielbastidas_2-1682982926424.pngdanielbastidas_3-1682982958677.png

 

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.

 

danielbastidas_4-1682983159612.png

danielbastidas_5-1682983200371.png

 

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

View solution in original post

5 REPLIES 5
danielbastidas
Regular Visitor

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

danielbastidas
Regular Visitor

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

danielbastidas_0-1682982627892.png

 

2. Create a slicer with this field containing the name of the pages and add a button to apply the selection

 

danielbastidas_1-1682982729562.png

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

 

danielbastidas_2-1682982926424.pngdanielbastidas_3-1682982958677.png

 

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.

 

danielbastidas_4-1682983159612.png

danielbastidas_5-1682983200371.png

 

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.

 

danielbastidas
Regular Visitor

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 

danielbastidas_0-1682885870801.png

 

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:

danielbastidas_1-1682885955762.png

 

3. Use the created field in the visual

danielbastidas_2-1682886048218.png

 

4. Create the filter with the column we added to the parameter

danielbastidas_3-1682886134517.pngdanielbastidas_4-1682886190830.png

 

danielbastidas_5-1682886223323.png


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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.