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. :)
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?
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.
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!
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
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.
@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!
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 :)
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?
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
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
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 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
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
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?
@@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?
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!
@@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
Kudos for you, man, for finding an extremely convoluted way to perfom a simple task of hiding column in a matrix. Not only it is difficult to undestand and manage, I will probably forget all about it in few months time, and I'll completely loose track of it. Moreover, your method kind of abuse the concept of calculation groups, as it doesn't provision for the reduction of the overall number of measures. It is mind boggling to me that one have to go all this trouble just to put some numbers in a matrix. But that Microsoft for you. I'm wandering were do they find all these great, great coders, and were do they find the managers which approve such great work. I think this is MS true specialy. Create SW which you have to work for rather than the other way around. It is hard to understand how MS could have neglected to provide such a simple feature already 3 yrs after you posted this video.
I’d love to hear your solution for this requirement then. Since mine is convoluted and abusive. What’s the simple solution for this, when it’s a hard requirement for the client? 🙂
@zvikabar-kochva3641: Lodging your complaints about Power BI in the comments on a @HavensConsulting RUclips video won't have any effect, because Microsoft will never see them. Microsoft doesn't even monitor the comments on their own RUclips channels, let alone all of the independent Power BI content creators like @HavensConsulting. You're basically just screaming into the void here, so if you're really that upset about what you perceive to be a problem with Power BI, why don't you contact Power BI Support or create an idea on the Power BI/Fabric community ideas website?
@@HavensConsulting , that's probably my bad English, but the criticism was entirely toward MC, not your solution. The thing is if you stick with the stock visuals, I don't think there is another solution. However, Zebra has visuals in which one can suppress a column if it contains no data. That said, I still think your solution, though produce the required funcionality, is convoluted. I applogize for using "abusive". It was distasteful.
@@zvikabar-kochva3641 appreciate the context, yeah I wasn’t sure if you were criticizing the technique or Microsoft, ha ha. Yeah, I definitely wish this was easier, but at least it is some solution for people, and hopefully it does improve in the future!
@@zvikabar-kochva3641 one newer solution as well, is to use field parameters, with measures, the column will automatically disappear, if the measure has no data in it
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
Just be careful with this calc group as it will replace any measure, and this may bring unexpected consecuences. It's safer to scope it to a certain base measure (e.g. [dummy]) and leave other measures as SELECTEDMEASURE () (i.e. Untouched)
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. 🙂
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. :)
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?
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!
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!
What an awesome technique! Works beautifully and really intuitive & fast - TOP! Thanks once more, Reid! 👍🙏
My pleasure!
This is awesome!! Just wondering how to get the conditional formatting going on certain columns ?? Any Idea
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
Great video Reid! I’m already thinking for some real use-cases 😎. Thanks for sharing!
Lots of good use cases for it :)
You are the saviour, awesome video!!
Great sample! Thanks for it!
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.
@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!
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 :)
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 brilliant, really clean
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
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
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
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
Cool.. and supernecessary!! 1000 Thanks!!! 👍
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
Great video, very helpful!
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 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?
Brilliant !
Another OOB, really nice
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 :)
It's amazing 👏
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
Simply amazing, thank you
Glad you enjoyed it!
Kudos for you, man, for finding an extremely convoluted way to perfom a simple task of hiding column in a matrix. Not only it is difficult to undestand and manage, I will probably forget all about it in few months time, and I'll completely loose track of it. Moreover, your method kind of abuse the concept of calculation groups, as it doesn't provision for the reduction of the overall number of measures. It is mind boggling to me that one have to go all this trouble just to put some numbers in a matrix. But that Microsoft for you. I'm wandering were do they find all these great, great coders, and were do they find the managers which approve such great work. I think this is MS true specialy. Create SW which you have to work for rather than the other way around. It is hard to understand how MS could have neglected to provide such a simple feature already 3 yrs after you posted this video.
I’d love to hear your solution for this requirement then. Since mine is convoluted and abusive. What’s the simple solution for this, when it’s a hard requirement for the client? 🙂
@zvikabar-kochva3641: Lodging your complaints about Power BI in the comments on a @HavensConsulting RUclips video won't have any effect, because Microsoft will never see them. Microsoft doesn't even monitor the comments on their own RUclips channels, let alone all of the independent Power BI content creators like @HavensConsulting. You're basically just screaming into the void here, so if you're really that upset about what you perceive to be a problem with Power BI, why don't you contact Power BI Support or create an idea on the Power BI/Fabric community ideas website?
@@HavensConsulting , that's probably my bad English, but the criticism was entirely toward MC, not your solution. The thing is if you stick with the stock visuals, I don't think there is another solution. However, Zebra has visuals in which one can suppress a column if it contains no data.
That said, I still think your solution, though produce the required funcionality, is convoluted. I applogize for using "abusive". It was distasteful.
@@zvikabar-kochva3641 appreciate the context, yeah I wasn’t sure if you were criticizing the technique or Microsoft, ha ha. Yeah, I definitely wish this was easier, but at least it is some solution for people, and hopefully it does improve in the future!
@@zvikabar-kochva3641 one newer solution as well, is to use field parameters, with measures, the column will automatically disappear, if the measure has no data in it
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
Just be careful with this calc group as it will replace any measure, and this may bring unexpected consecuences. It's safer to scope it to a certain base measure (e.g. [dummy]) and leave other measures as SELECTEDMEASURE () (i.e. Untouched)
Good catch!
Awesome
Cool
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. 🙂