Conditionally Show/Hide Matrix Columns Using Calculation Groups
HTML-код
- Опубликовано: 2 авг 2024
- Learn yet another way to leverage calculation groups to conditionally show or hide column metrics based on either: if blank conditions, or slicer selections in Power BI.
BECOME A CHANNEL MEMBER 🎉
-- / @havensconsulting
LET'S CONNECT! 🧑🏽🤝🧑🏽 🌟
-- / havensbi
-- / reidhavens
-- / havensconsulting
VIDEO CHAPTERS 🎥
0:00 - Start of Video
0:32 - Content Intro
2:33 - Explaining Calculation Group
3:41 - Explaining Slicer Filter
HAVENS CONSULTING PAGES 📄
Home Page - www.havensconsulting.net
Blog -- www.havensconsulting.net/blog-...
Blog Files -- www.havensconsulting.net/blog-...
Files & Templates -- www.havensconsulting.net/files...
Consulting Services -- www.havensconsulting.net/consu...
Online Courses -- www.havensconsulting.net/onli...
Contact & Support - www.havensconsulting.net/conta...
EMAIL US AT 📧
info@havensconsulting.net
#PowerBI #powerplatform #microsoft #businessintelligence #datascience #data #dataanalytics #excel #powerapps #datavisualization #dashboard #bi #analytics #powerquery #dax 
Наука
This is very, VERY useful! I just built a massive matrix visual that my clients wanted, and that I hate myself. This will help me to make it much more cleaner. Thank you!
I just wish you'd do less of those very long collab videos that are impossible to watch in their entirety, and go back to these brief but very useful gems.
Hi Vlad, since my channel's launch I've almost always released a video every Tuesday. I still do that today. The number of brief videos haven't decreased, I only added livestreams on Friday's. So if anything you're getting more content, not less. :)
What an awesome technique! Works beautifully and really intuitive & fast - TOP! Thanks once more, Reid! 👍🙏
My pleasure!
Great Thanks Reid. The solution I have been searching for the last 2 weeks. Now I don't have to control my different columns in a matrix with bookmarks and buttons!
This is brilliant, really clean
You are the saviour, awesome video!!
Great video, very helpful!
Great video Reid! I’m already thinking for some real use-cases 😎. Thanks for sharing!
Lots of good use cases for it :)
Great sample! Thanks for it!
Brilliant !
Cool.. and supernecessary!! 1000 Thanks!!! 👍
It's amazing 👏
This is great, I've been using a similar technique to reduce the number of columns in a large matrix. A down side of using calculation groups this way is you lose the ability to click the column header to sort. I got around this by creating a specific placeholder measure using a disconnected table and SELECTEDVALUE() that allows users to chose what to sort by using a slicer. I use this value in a switch statement to determine what value is the "grand total" and set the matrix to sort by this measure. Not ideal, as I would rather have users click the column header rather than introduce another slicer to the page, but it works well enough.
That's a clever workaround, thanks for sharing!
Simply amazing, thank you
Glad you enjoyed it!
Another OOB, really nice
Awesome
This is awesome!! Just wondering how to get the conditional formatting going on certain columns ?? Any Idea
Excent!... I have a question, how do you "fix" the columns in a matrix? for example, you have a Month column for sales, and you display A or B stores, and A only had sales Jan-Jun and B has only Jul-Dec... But you want to display the full year when selecting one or the other one. I know that when you select both, all the Yyear will be displayed, but I want to show all the year as well when A or B are selected only (probably display 0 sales on the months with 0 data)... thoughts? ideas?
Hi if i have a large text field in a Table that shows Several lines of say a telephone correspondence but i was my table to show only the first 50 chars ( so each row is only 1 line) unless i expand it back up to see all text is this possible.
Perfect! I'm just having a lot of trouble to make conditional formatting work with this. It keeps formatting all values from the matrix. Do you have any tips on how to do it? Thanks!
Unfortunately without looking at the DAX I wouldn't know what the issue might be. Perhaps could you share the PBIX? You can email me at info@havensconsulting.net
@@HavensConsulting Sure, I'll prepare a test pbix and send to you
I tried same with basic (where each calculated items assigned to its individual measure name only, no switch used) but it's not working, and evaluating matrix with what measure I put in value section only. IS THERE ANY SPECIAL STEPS YOU've USED TO MAKE IT WORK. Also When I remove and add again calculated group column (Name) from matrix in your report it starts showing sales only (i.e. what measure we used in values section). Any ideas why this is happening?
This is really great but since this was recorded back in 2021, is there a better way to accomplish this hiding of matrix table columns? Maybe a more efficient way than creating calc groups?
It's still the only real way to do it with a native matrix visual. Otherwise custom visuals like InfoRiver do it super easily with a UI that allows you to easily toggle on/off the visibility of any row, column, or group :)
Hi,
Just wanted to let everyone know that, although this is a great solution in some situations, this way of working has a big limitation if you want to work with tooltip pages (showing an extra page with graphs/data when hovering over your matrix variable). It completely breaks the tooltip page functionality in fact.
Since using these kind of calculation groups, in which the data is not related to the SELECTEDMEASURE(), my overlays simply show the number on which I'm hovering and no longer the drilldown filter variables setup in the overlay page.
Since my reports heavily rely on this, I probably will have to abandon this approach unless I find some way around it.
Great callout!
So calculation group logic can be ignored as well, for a tooltip. ALL(CalcGroupTable) in the measure used on the tooltip page.
Additionally you can have the calculation group ignore certain measures, or focus only on a select number of measures using the SELECTEDMEASURENAME function as well :)
Two potential solutions for this at least
Hi, could you please suggest a way to colour the background for a selected of these columns alone? I am facing issues with conditional formatting with calculation groups. I tried using selectedvalue() to access the calculation item name, but I am not getting the name of calculation item using that. Any ideas on a resolution would be of great help. Thanks.
I'll have to take a look at that. But I don't believe I can dynamically conditionally format each individual column with a measure in the current version of Power BI Desktop
@havensconsulting Thank you, but I am totally stuck. I've re-watched so many times and I can't find the part where you show the 'Name' measure. I see the DAX for 'Name Filter' but not name. What DAX code did you use for 'Name'?
Hi Anna, sorry if that got missed! You have access to download any of the PBIX files from my videos by going to my Blog Files page, where you can find all the code there as well :)
The link to the Blog Files page is in the description of the video. Hope this helps!
Cool
is there a way to do nested group in calculation group to use in matrix visual? For example, group all related calculation items under one group.
Could you provide a grid image or visual concept of this example? Perhaps a mockup in an Excel file? Email us at info@havensconsulting.net
Should i create the calculated groups in the tabular editor or in Power BI?? what is unit here?
i would appreciate your reply
If you have tabular editor, that's my best spot to make them. Mostly because you can copy/paste the calc items super easily for repeat patterns (MTD, QTD, YTD, etc). Can't do that in Power BI Desktop. But both let you create the same result! Desktop is just a bit slower to make
great video. but i want to hide column that contain data and only keep one column depending on the value of another parameter. so i want to show a column acvordibg to the value of another column. help
Unfortunately, without gathering some additional information about the requirements I don't have enough information to solve this from your initial message. We provide help desk consulting if you're looking to build out the requirements for a report. :) www.havensconsulting.net/contact-and-support
how can i filter the calculation items in a matrix visual depending on slicer from a disconnected table? And it doesn't not auto refresh the matrix visual as well.
SWITCH (
TRUE (),
SELECTEDMEASURENAME () = "Name Filter",
IF (
ISBLANK ([Mandays Budget]) ||
SELECTEDVALUE('Display Type'[DisplayMode]) "ManHours"
, 0, 1 ),
[Mandays Budget]
)
Hi there, thanks for reaching out! So are you wanting to make a slicer selection from a disconnected table, that then shows or hides calculation group columns in a matrix table?
@@HavensConsulting yes i am.
@@zchase6631 I think I'll need more context from looking at the PBIX file. If you have a workbook I could take a look at sometime over the next few weeks I can see if I can figure out what you need done. You can email me at info@havensconsulting.net
For some reason, I am no longer able to assign a single number to a measure as described in the video and use it as a matrix visual level filter. I know I was able to do it in the past in Power Bi. I'm using the May 2022 release of Power Bi. Can you confirm that this is still possible?
It seems its possible to do this on a line chart but not in a matrix visual. Maybe Microsoft changed something with the matrix?
Hi Evn, so I have a visual level filter on my name slicer, but there isn't a visual level filter on my matrix table. What part of the video do you see me doing this? I checked the workbook that is available for download from my Blog Files page, and the confirmed no visual level filter that I saw in there on the matrix table, so let me know :)
@@HavensConsulting sorry I am doing the filtering directly on my matrix, not in a slicer, my mistake. It turns out that you can't filter by a measure on a matrix if you have a calc group added to the matrix table. You have to remove the calc group, apply the measure filter, and then readd the calc group. Sorry for the confusion!
@@evnpresson7258 No worries! Glad you got it figured out as well :)
That is awesome but can you tell me how to implement this hide and show feature for columns. I have columns which are not only numeric values, its have string values as well. Please tell me how to do this?
Sure, are you referring to the "Hide Column from Report View" feature?
@@HavensConsulting Yes. I want to hide that columns from matrix visual in the report.
@@HavensConsulting actually let me clear that out. I have a report that is related to some transactions. Now For different transactions different columns are used. Suppose I have A,B,C,D transaction types. For A, I have columns a,b,d,e. but For B, I have columns a,b,c,f. So for B transaction d and e column value is blank. But I want to show a single report where user will choose transaction type , based on that report will show that transaction table with consecutive columns, that will remove blank columns and will only show the columns which have values.
so my goal is, user will select B transaction and we will show only a,b,c,f columns so that d and e will be hidden. I think this will help you to undersand. Please help me to resolve this. Thanks for replying by the way.
@@ManaliRay thanks for the explanation. This is complex enough that I'd need to spend some time with the client and the file to implement this solution. We do offer help desk hours for just these situations. The Power BI community page where you can submit files is another great option as well! www.havensconsulting.net/contact-and-support
how does the formula need to look like if I only have the Year slicer and not the name slicer?
Could you elaborate on your scenario so I can understand it a bit better?
How to create separator between calculation items in calculation group?
Could you elaborate on what you mean by seperator?
@@HavensConsulting between certain calculation items, i would like to have a empty column display on the matrix.
@@zchase6631 if you wanted a "spacer" column between the calc groups then you'd need to create a "blank" calculation inside of the calculation group using Tabular Editor
Can you please provide that pbix file
It's available from our Blog Files page. Link is in the video description, and I'll include it here as well. :)
www.havensconsulting.net/blog-files
Hi sir,
Good morning
This is gowthami
I have one question related to hide a column (measure ) from table visualization .
That column I have used for sorting purpose I dnt want to showcase to users in table visualization and also after extraction also
Plaese can you help me on that,it's very urgent requirement
Thanks
Gowthami
Hi there, something like this should help
ruclips.net/video/Ch78LbsfsJs/видео.html
the video is too fast
RUclips has a playback speed option that will help if it's too fast for you. Click the gear icon in the lower left, and set the playback speed there.
you speak too fast, hard to catch
Thanks for the feedback. RUclips has a great speed control feature actually. If you click the gear icon you can slow down the video to more easily follow along. 🙂