Power BI P&L Report with Option to Hide / Show Columns using Fields Parameter (Alt to Freeze Pane).
Вставка
- Опубліковано 9 лют 2025
- Incorporating a new Power BI feature… newly available in May 2022, New Field Parameter
Can be be an alternatives to those that wants to Freeze Panes in Power BI.
Previously, it was difficult to design a user interface that enable Power BI report user, to select which columns that they want to hide or show in the Power BI P&L report.
For example, in the P&L report, users may want to see comparison vs budget, instead of vs forecast.
or... comparison vs Last year, instead of vs Target...
Some users may also want to see % variance instead of $ variance in the P&L report.
With New Field Paramater.... a newly available Power BI feature, recently released in May 2022, these are all possibilities. Users now have flexibilities, to hide or show columns, by clicking options to display.....
This option can also be an alternative for the lack of FREEZE PANES, to show selected columns, and
Hope you enjoy this video... and learn something useful for you to apply in your future Power BI P&L report.
See you next time.
Cheers,
Isabella Susilowati
#lighthouseanalytix , #worksmarterplayharder, #powerbitutorial, #powerbi, #powerbitutorialforbeginners
LA56
I have seen 4-5 videos before this, and when I use tricks from these videos, i need to convert my report into import mode but using technique in this video I don't need switch from direct query to import and I got my charm. Thank you
Glad it helped!
I have been searching for something similar and this works really well. The explanation is just precise and perfect. Thank you for making our life easier :)
Glad it helped!
Works like a charm, thank you Isabella!
you are welcome !!
Can you add a column to this matrix for group heading without duplicated measures?
how to add index for each column header
I have year slicer where I am giving selected year sales and previous year sales customer wise
but for first year there is not previous year data then I want to hide last year sales ???How to achieve this any suggestions ????
loved the demo and the way you explained it, made it so easy:)
Glad you liked it!!
can I have a single option in a slicer to hide/show multiple columns ? for eg. a single slicer option say 'Var' and it show/hides 'Var % TY vs LY' and 'Var TY vs LY' at the same time ?
Thanks! Is there any way to combine columns for this? for example, my data contains multiple columns for Capital expenses and multiple columns for Operational expenses. I would like three scenarios, 1: show columns pertaining to or containing "CapEx" by selecting "CapEx", 2: show columns pertaining to or containing "OpEx" 3: Show all
Have you considered displaying those selections as rows instead of columns... Then you can use your filter panes on the right to make various selections
Great video! I was wondering if there is a way to hide columns that are null. Thanks
I have applied the same in my case, but I am not getting total for measures in the down of table. Please suggest
sorry a bit difficult for me to help to trouble shoot without seeing the file. Have you turned on row total option in the table setting?
I have used above format but all numeric fields are changed into Text . Now , Unable aggregate those numeric columns same as Unable change datatype for Text into numeric .
Please suggest
Sorry I'm not 100% clear... on the problem that you are experiencing. Can you try converting your data type back to numeric?
@@LighthouseAnalytix Yes i am trying but unable to change text into numeric
hello, thanks for the helpfull video. Why, if I follow step by step the video, does my table lose the conditional formatting? Moreover, I am unable to reapply it to the range that includes all the variables. Could you help me?"
hm... I haven't tried this yet... keen to hear if you are able to get this sorted
Hallelujah this worked! Thank you!
Great to hear! So happy for you :)
Great to hear! So happy for you :)
What if I have several columns that need to stay visible at all times and I want to hide and unhide "ranking" columns. Is there a way to write this to were the slicer does not show the option, but it still stays on the table?
Filtering the slicer will hide the selection and will show the column, if you then select all on the slicer. However if you actively select from the slicer the column will disappear.
how about if you create two pages... one table with ranking column shown... and another table with ranking coloumn not shown.
Then merge them into one page with two tabs (with ranking & without ranking).
ua-cam.com/video/Ng1EylK8KXU/v-deo.html
Thanks, this is helpful.❤️
Keep going!
You're welcome 😊
My question is, could we disable show data point table on publish report? So, public couldn't see dataset / table behind that visualization. My case is on publish report on website.
Are you referring to limiting the ability of report user to download the underlying data from your Power BI Visuals, in Power BI Service?
If so, go to File || Options & Settings || Options
Then scroll down on the left pane to Current File | Report Settings
Then on the right pane, go to Export Data | Select: Don't allow users to export data from Service or Report Server.
Can I add rows in Field Parameters?
Absolutely, just modify the dax, and add additional rows
@@LighthouseAnalytix can you tell me how?
Hello , is it available in power bi desktop?
yes absolutely
Great and awesome.
Thank you! Cheers!
Hi thank you for the video and the explanation. I need to add more difficult. I need to show the column of my table by selecting a value on a different slicer. There is this possibility? If i choose Value1 then the column to display are 1,2,3 if i select Value2 the column to display are 4,5. I can do this with a particular measure?
Thank you!
The solution is to add a column to this structure with the key and connect it to the other table. So when you filter the other table with a slicer automatically you will filter the showed column.
Thank you so much!
I have never tried that before.
Can I assume that 1 may be a relevant columns for Team 1 or situation 2?
2 may be relevant columns for Team 2 or Situation 2?
Perhaps also worth exploring:
(1) Setting up multiple tabs in a page. Create 1 tab for each team.
I have a video about this. studio.ua-cam.com/users/videoNg1EylK8KXU/edit
(2) Setting up bookmarks that store relevant filter settings. Create 1 bookmark for each team.
I also have a video about this. studio.ua-cam.com/users/videotoRM2gcNc1Q/edit
Thanks for sharing. Wondering it is possible to have none of the measures displayed. ie unchecking all slicer option will lead to everything showing (no filter)
Not that I know of... Keen to find out if other people may know some tricks...
really calm voice,perfect
thank you :)
Hi, thank you for making this. I have a request from a user that wants certain columns to be hidden. But the columns used in the parameters still show up even if they were not selected. I guess it is by default.
Can we do this in matrix table? ie I have QoQ and YoY measures, but I only want to show QoQ for last qtr, and YoY for last year, instead of showing all QoQ and YoY columns
haven't tried it with matrix yet... please share with us it it words...
Tks miss, you're awesome
only it works for measures not for columns
Wonderful
Thank you! Cheers!
awesome. super useful!
thank you !!
Thank you so much dear lady :)
No problem 😊
great way!
Thank you !!
After a couple or hours using this feature I noticed some problems with the order when you remove / add new columns. Sometimes this feature is not ordering the columns properly even if you defined the order in script, it ends up putting the category as the last column instead the order defined earlier. Another problem is the drilldown, it doesn't update the table to the bottom level automatically, so the user needs to expand all down in the hierarchy manually. This can be a pain for the final user. :\ This is really a shame, this feature seemed promissing.
I haven't come across something like this yet... Pls do share some workaround or solutions if you come across one....
@@LighthouseAnalytix Sure! Thank you for the reply :)
OMG Can't thank u enough
you are welcome :)
thanks ma'am :)!!!
Most welcome 😊
Can this be done for Matrix table as well?
I tried for table and it works like a charm, however can you please make one video on doing the same for Matrix table as well?
thanks for the idea... perhaps something that I can explore in the future.
@@LighthouseAnalytix Thanks you very much mam.
Thanks a lot.
Most welcome!
Great video , keep it up , question : is there a way to enable individual users to save the created configuration/user , so that in the future when they see the report they don't have to remove again the columns. I have 160 columns report and each user is interested in a different subset of those 160.
Yes, using bookmark !!!
I have several videos on how to use bookmark to save a desired view....
ua-cam.com/users/shortsTTFOmbu7ftc
This video is a great introduction to personal bookmark.
orang Indo ya Buk?
Betul :)
@@LighthouseAnalytix mau donk belajar banyak dari Ibu ...
Ummmm, thanks.
:)