Conditionally Show/Hide Matrix Columns Using Calculation Groups

Рет қаралды 33,598

Күн бұрын

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

Пікірлер: 70

@gerhardvanderwesthuizen1856 3 жыл бұрын

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!

@VladMZ 3 жыл бұрын

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.

@HavensConsulting 3 жыл бұрын

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. :)

@businessinsights_AlexRobe 3 жыл бұрын

What an awesome technique! Works beautifully and really intuitive & fast - TOP! Thanks once more, Reid! 👍🙏

@HavensConsulting 3 жыл бұрын

My pleasure!

@ramank22787 2 жыл бұрын

You are the saviour, awesome video!!

@ashleytaaffe9423 3 жыл бұрын

This is brilliant, really clean

@cristian.angyal 3 жыл бұрын

Great video Reid! I’m already thinking for some real use-cases 😎. Thanks for sharing!

@HavensConsulting 3 жыл бұрын

Lots of good use cases for it :)

@johansantacruz6464 Жыл бұрын

Great sample! Thanks for it!

@terpstry 3 жыл бұрын

Great video, very helpful!

@antoniogarrido3058 3 жыл бұрын

Cool.. and supernecessary!! 1000 Thanks!!! 👍

@marksulsh1234 2 жыл бұрын

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.

@harsimranjeetsingh6101 2 жыл бұрын

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?

@gmartinezm Жыл бұрын

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?

@ranbirwadhawan6327 Жыл бұрын

This is awesome!! Just wondering how to get the conditional formatting going on certain columns ?? Any Idea

@didierterrien6933 3 жыл бұрын

Brilliant !

@tajuddinnoorani8336 3 жыл бұрын

It's amazing 👏

@juansepowerplatform 3 жыл бұрын

Simply amazing, thank you

@HavensConsulting 3 жыл бұрын

Glad you enjoyed it!

@timgray7859 2 жыл бұрын

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.

@HavensConsulting 2 жыл бұрын

That's a clever workaround, thanks for sharing!

@andrejaar2136 3 жыл бұрын

Another OOB, really nice

@gopikakoranath1201 2 жыл бұрын

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.

@HavensConsulting 2 жыл бұрын

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

@AnnaBlanchette-ew9fx 7 ай бұрын

@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'?

@HavensConsulting 7 ай бұрын

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!

@rvian4 2 жыл бұрын

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!

@HavensConsulting 2 жыл бұрын

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

@rvian4 2 жыл бұрын

@@HavensConsulting Sure, I'll prepare a test pbix and send to you

@walteradamsbe 6 ай бұрын

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.

@HavensConsulting 6 ай бұрын

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

@zchase6631 2 жыл бұрын

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.

@HavensConsulting 2 жыл бұрын

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

@evnpresson7258 2 жыл бұрын

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?

@HavensConsulting 2 жыл бұрын

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 :)

@laionegan 5 ай бұрын

Awesome

@zchase6631 2 жыл бұрын

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] )

@HavensConsulting 2 жыл бұрын

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?

@zchase6631 2 жыл бұрын

@@HavensConsulting yes i am.

@HavensConsulting 2 жыл бұрын

@@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

@yousefesfahanian2636 8 ай бұрын

Should i create the calculated groups in the tabular editor or in Power BI?? what is unit here? i would appreciate your reply

@HavensConsulting 8 ай бұрын

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

@ut0rnp 2 жыл бұрын

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

@HavensConsulting 2 жыл бұрын

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

@evnpresson7258 2 жыл бұрын

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?

@evnpresson7258 2 жыл бұрын

It seems its possible to do this on a line chart but not in a matrix visual. Maybe Microsoft changed something with the matrix?

@HavensConsulting 2 жыл бұрын

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 :)

@evnpresson7258 2 жыл бұрын

@@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!

@HavensConsulting 2 жыл бұрын

@@evnpresson7258 No worries! Glad you got it figured out as well :)

@ellenbohr3537 Жыл бұрын

how does the formula need to look like if I only have the Year slicer and not the name slicer?

@HavensConsulting Жыл бұрын

Could you elaborate on your scenario so I can understand it a bit better?

@zchase6631 2 жыл бұрын

How to create separator between calculation items in calculation group?

@HavensConsulting 2 жыл бұрын

Could you elaborate on what you mean by seperator?

@zchase6631 2 жыл бұрын

@@HavensConsulting between certain calculation items, i would like to have a empty column display on the matrix.

@HavensConsulting 2 жыл бұрын

@@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

@ManaliRay Жыл бұрын

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?

@HavensConsulting Жыл бұрын

Sure, are you referring to the "Hide Column from Report View" feature?

@ManaliRay Жыл бұрын

@@HavensConsulting Yes. I want to hide that columns from matrix visual in the report.

@ManaliRay Жыл бұрын

@@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.

@HavensConsulting Жыл бұрын

@@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

@manishkatiyar2403 3 жыл бұрын

Cool

@nayabrasool5752 2 жыл бұрын

Can you please provide that pbix file

@HavensConsulting 2 жыл бұрын

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

@gowthamipm2350 5 ай бұрын

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

@HavensConsulting 5 ай бұрын

Hi there, something like this should help kzfaq.info/get/bejne/ec5na6-T2MzXeqc.html

@ut0rnp 2 жыл бұрын

the video is too fast

@HavensConsulting 2 жыл бұрын

KZfaq 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.

@abbyguan1105 2 жыл бұрын

you speak too fast, hard to catch

@HavensConsulting 2 жыл бұрын

Thanks for the feedback. KZfaq has a great speed control feature actually. If you click the gear icon you can slow down the video to more easily follow along. 🙂