Power BI - Dynamically Add or Remove Measures from Visual

[@harrisonboote669]

Absolutely love this video! Best I've seen of anyone explaining what the coding is doing whilst you are writing it! Made an account just to subscribe and make this comment! Well done!

[@BIElite]

Thanks Harrison! I appreciate it!

[@domvicchiollo6304]

Really appreciate the simplicity in your tutorials. Thank you for your help!

[@BIElite]

Glad to hear, Dom! Thanks for watching.

[@sarva0483]

Awesome trick! Advise how can we achieve this same logic in Matrix table, for ex: Any one measure is selected then other 2 measures (columns headers) should not be shown in the matrix

[@andybolton7345]

Hi, Only just getting into Power Bi and even though it's 4 years in, this video has been a gamechanger...THANK YOU!! As a follow on question, is there a way to connect multiple slicers to the same graph? Example I have the same 3 options for 6 possible top level sections (A1,A2,A3,B1,B2 etc), how do I place all of this into the same/different slicers? Hopefully this makes sense.....happy to provide more detail

[@rosswaterston]

Great tip and so simple. This goes above the normal switch functions to switch between ranges that I have used so often now, having the ability to set up a chart that people can view exactly what they want only enhances that.

[@BIElite]

Thanks Ross! I'm glad you see the value over the usual switch. I just had a client request this thinking it's a built-in feature so I'm glad this method is possible

[@reticulan]

Great video! Is there a way to display the items on the legend depending on what is selected? Thanks!

[@Nick.Anselmo]

Interesting way you did this, like your creativity. I accomplished the same result using SWITCH function on top of that new table of selected metrics.

[@BIElite]

SWITCH is super useful, I agree. I just kind of recycled the code from my default slicer video for this purpose. Thanks for the input!

[@kevinlabore1726]

for similar scenarios -- are there advantages to using shown method over Switch (or vice-a-versa)? My first thought when I saw title was to use Switch

[@BIElite]

Kevin, just saw this response. SWITCH will only allow for one measure at a time. This method allows you to show as many measures as you want at the same time!

[@kevinlabore1726]

@@BIElite ah, that would be advantage

[@vkishen01]

Above steps not worked for me with Direct Query Data. Any workaround to accomplish switching measures for Direct Query ?

[@simphiwe_stb]

I have been looking for something like this for a while, thank you

[@InteligenciadeNegocios]

Awesome video! I just want to know if is there any possibility that this method works with a leyend? i tried to add a leyend it didn't work. Thank you!

[@learve22]

Great tutorial, all that in 7 mins GOAT

[@danielsiffredi8403]

How do we add /remove labels based on selection? Great video BTW!!!

[@powerbiforsports9518]

Great info! In my case I just wanted to toggle on/off 1 custom measure that I had in the 'Line Value'. I just created two rows on the table, SHOW and HIDE, and then just used one measure. Thanks!

[@piotrkulas574]

Hi, great video, really helpful! Is there any solution if I want to return more than one measure? As in your example, if I choose "Labor" I want to present more than one measure with this selection. Thanks!

[@regisoconnor7133]

Thanks for sharing - I am building a viz for MakeoverMonday and this video helped me so much!

[@BIElite]

Great to hear, Regis!

[@Yours_raj308]

Any solution is there to change legend also dynamically on line chart ? Thank you

[@eddyjawed4871]

I love this thank you really helpful. BTW I have problem where the label names of all measures are appearing on Y axis despite it only showing one at a time. How can I get it it to only show the visible measures label on the graph?

[@DynamicSphinx]

Is there a way to dynamically add or remove the legends as well along with the lines based on the slicer selection? Thank you

[@jakkojusatish9560]

Any solution is there to change legend also dynamically..!!

[@jonathanvanderberg4096]

This is really cool, thanks for this. I've tried applying this process to a table chart, the end result is not exactly the same... is there a way to do this by only displaying the field selected, the columns remain in the table chart visual as BLANK even when not selected, not sure how to hide unused columns in this view... any ideas?

[@BIElite]

Hi Jonathan, this video is a bit antiquated because of the new Personalized Visuals feature. Video here: kzbin.info/www/bejne/m3-zZGmlr9ihqdU You should be able to do what you are hoping.

[@mytubeview]

Thanks for the video! It solved my problem to get a calculated cost line to shows in this report!

[@suprachavan7102]

I have just started with Power BI and this is a great tutorial!

[@BIElite]

Thanks Supra! Glad you found the channel!

[@JasbirSingh-bw7dt]

Can you share the last video which you mentioned in this tutorial?

[@guirecpiriou9931]

Hi, thanks a lot for the tutorial, very clear as usual! Do you think there would be a way to avoid the copying all the measures by using the calculation group method instead?

[@CimeronMcDonald]

A great tutorial, really liked the step by step instructions. Is there a way to use this with columns (Variables), instead of measures.

[@halaghali5011]

I was able to do that trough adding a sum function before the column name

[@shrushtishah1212]

Thank you for the informative video!Is it possible to select multiple dimensions instead of measures?I want to create a table that would dynamically add /remove dimension columns using slicer.

[@varunjajoo93]

Thanks for the Video, I have a doubt though: Can we remove the header/label too (you can see at 5:00 in the video 'Labor by Date' ?

[@adijobs2582]

is there any way to hide the measures which are unselected ?. My users are getting confused since all the data labels are appearing irrespective of selection of measures

[@BIElite]

Unfortunately, no. This is the drawback of using this method. If you only need one selection at a time, I recommend using the SWITCH function to select a single measure.

[@chakhil8000]

Hi good solution ..but how can you control legends in chart?it is still showing respective legend even though you havent selected?

[@saisatya5525]

Good video, Can I include instead of measure in column in dynamic way

[@BIElite]

Yep, check out my Dynamic columns videos. There are a couple.

[@nboisen]

Awesome application of simple techniques. Very useful. And clearly explained. Keep it coming!

[@BIElite]

+nboisen Thanks! Always appreciate the support

[@abdulazad444]

Hi, I am pretty new to Power BI, I wanted to show and hide columns in a table visual based on slicer selection, columns are like Effective_Date, Purchase_Date, Quantity, Order_Number. How can I achieve this. I have seen so many videos of show/hide columns on measures not on dimension or attribute or date columns. Your video on this will be much appreciated.

[@BIElite]

Hey Abdul, I just replied to your comment on my other video here: kzbin.info/www/bejne/bJvIhHyIpc-pZq8

[@Casbah354]

Hello, Is it possible to dynamically add/remove the axis and actually the 2 options at the same time : not having any axis or having axis (and being able to also dynamically switch axis). Indeed I have a stacked bar chart of which I want to have the overall revenue per product category but also to have one stacked bar chart (still distributed by product category) per region or country. So, to sum up : to be able to dynamically switch between the overall, the region and the country. Thanks

[@bhawanakant7595]

​ @RADACAD can we do the same thing with columns/dimensions instead of measures. Example, the way you are switching view by measures (cost, profit etc), can we do the same thing and switch dimensions (country, zone, region etc) instead of measures.

[@kishoreyc]

You are a super guru. I have used it in my model and My boss will like for sure.

[@BIElite]

Haha thank you Kishore.

[@dragonsdata]

Hi! Great video! Very helpful! :) I tried to use this approach in a matrix. But I have a problem, that it doesn't give me Totals. Do you know how to solve it? I have measures Fact and Budget by KPI in rows and by Year, Month in columns, so I would like to see Year Total for Fact and Budget by KPI. Thanks in advance!

[@milosacimovic8796]

Can this be done with matrix?

[@kolimirahamthulla4556]

Hi, this technique is very useful. But when it comes to Legend - currently all the all the 3 measures were appearing. But I need to see when select only one measure I should only those selected measures in the Legend !! Is there any way to control the legends based on the measure selection. Thanks.

[@sadyaz64]

We learn Every day. Thank you

[@BIElite]

Hah yes we do!

[@BaraccoAndrea]

Hi, great info. Just a quick question, how can I make that if any of the filters are selected then none of the measures are shown in the graph, because as I have an stacked graph I want the measures to not be shown so only the total when there are not filters. Thanks!!

[@Imm23743]

Hi, I need a bit of help. Regarding IF( LogicTest, [Sum of Measure], BLANK() ) This syntax doesn't work me. Rather if I do IF( LogicTest, 'Tablename',[Field], BLANK() ) It works fine. However, if I want to use a measure that I made in data modeling i.e. IF( LogicTest, [Measure], BLANK() ) It just displays 0 when I have the correct slicer selected. Any ideas?

[@emmafeng55]

Hello thank you very much for the video. I tried your method it works quite well but just very close to what I expect. I tried to put measure into slicer by grouping customers into three categories. The method in your video shows the figures but I just want to filter the slicer and get the figure for the customer categories, is it possible to do?? really appreciate your help :)

[@artemyarosh817]

Thanks a lot you saved my life

[@MrAszpic]

I'm not able to reproduce the auto-hiding legend. Can you please confirm it still works that way when the measure shows only blank values?

[@deniswolff5021]

Great! Do you think this will also be possible in MDX in a multidimensional cube?

[@BIElite]

Sorry for the late reply Denis. I don't think you would be able to do this when hooked up to a cube. The functionality within power bi is severely limited when using cubes

[@pivotstream6433]

@@BIElite Denis, if you have these measures in Analysis Services and are surfacing in PBI or Excel, they should be just fine.

[@elwinwarsh7256]

This was amazing! Super easy to follow too!

[@BIElite]

Thanks Elwin!

[@MrErolyucel]

The unique value of information. Thank you

[@BIElite]

Thanks Erol!

[@jaxo116]

Hi BI Elite and everyone, I was able to apply this solution to set of measures based on a true and false column, so now if click false and true the right set of measures is being showed however the format is a problem, I'm not able to show the labels and axis info in percent format, it shows general format, and if the type is specified in the dax it throws the right number in the right format in a card but in the graphic shows nothing. My original measure is already formatted in percent. Any helpful ideas??

[@OluwatomisinSoetan]

Very Very Grateful for this!!! 😀

[@AhmedHassan-wt6lv]

really perfect and clear

[@motionaustralia]

Can you do this for a table

[@BIElite]

Check out the new Personalized Visuals feature: kzbin.info/www/bejne/m3-zZGmlr9ihqdU

[@riazahmedshaik]

I'm going to use this in my work. Thanks for the tip :)

[@BIElite]

Great to hear, Riaz!

[@andresdemz]

Hello there! This is a very nice and useful tutorial, I wanted to ask you if is possible to add let's say Sales from a product, and another option tu compare with Sales from other product, and to add a Slicer to filter that "Product B", which only affects that line chart, so you can do multiple comparations. Regardly, thank you very much for your help!

[@boyangeorgiev6191]

Can this be done for a waterfall chart?

[@zagaaru]

Where is the pbix file? I was willing to give up my email for it, too bad

[@ОлесяБовсунівська]

Great tutorial! Thank you very much!

[@sudipto606]

Thanks for the great help !! very helpful one!!

[@BIElite]

Glad to hear!

[@Grumbholdt]

Is there a way to use this method in a Matrix, but have the Measure remove completely from the Matrix when unclicked, rather than go blank

[@rodriguesngoma9656]

It´s very good and helpful

[@BIElite]

Thanks Rodrigues!

[@webkts607]

Nice tip.

[@martingoedhart2294]

Thanks, great video. However for some reason I need to specify the table name of every measure I want to reference, including the measures used within the measure. So putting [measure] after the 'LogicTest;' in the IF Statement returns an error that the value cannot be determined. tablename[measure] DOES work as long as that measure does not reference other measures, otherwise same case. I would have to change all my measurs to include the home table name in front of it: which is not best practice, or copy/paste the full measure content. Very strange behavior. Looks like it has to do with combination of SSAS connection and reportbound custom measures. If I create the measures on the SSAS Tabular model, it works.

[@8g8819]

Great information. Thank you

[@BIElite]

Appreciate the support as always giavo!

[@kanonsaga2004]

Thanks!!! Works perfect!!

[@BIElite]

Glad to hear that, Gustavo!

[@eddyjawed4871]

Thanks!

[@BIElite]

Thanks Eddy!! Didn't see this until now.

[@estebansuarezcastro7053]

OK, that's cool and easy, but right now I have 23 metrics to apply that trick. Is there any way to compare at least two of that 23 selected metrics dynamically? Let me know please if you have another video about it. Thank's anyway.

[@estebansuarezcastro7053]

PD: I'm working on a Balance sheet, and you know that in the accounting world they need to compare at least two rates (measures).

[@BIElite]

The new Personalized Visuals functionality is your friend: kzbin.info/www/bejne/m3-zZGmlr9ihqdU

[@pratikfutane8131]

Thanks for the great video!! Inspiring

[@BIElite]

And this is probably my second favorite trick lol

[@pratikfutane8131]

@@BIElite 😄 awesome

[@PoxHaunted]

Thanks! it worked!

[@janeqin6130]

that is very clever! I love your video. thank you so much for the tip

[@shailakurian9305]

Thank you for this

[@rodriguesngoma9656]

Really greatful

[@BIElite]

Thanks for watching!

[@nileshchouhan2850]

thanks for solution really helpful.

[@BIElite]

+Nilesh Chouhan no problem! Thanks for watching

[@ritanoori7326]

Thanks

[@BIElite]

No problem Rita!

Great Tip!

[@BIElite]

Thank you José!

[@shoaibrehman9988]

Brilliant 👏 👏 Logic

[@MrAszpic]

2 years later and I still come to this video for the reference. The legend doesn't respond dynamically now, though

[@jakkojusatish9560]

Any solution is there to change legend also dynamically..!!

[@waltherleonardo8652]

you are a genius!!!

[@BIElite]

Haha thanks so much Walther! Appreciate you watching the video

[@salosteysv]

It is interesting! Thanks!!

[@BIElite]

Thanks Станислав!

[@prachigorwadkar6969]

Amazing

[@hi_vishy]

Awesome

[@BIElite]

Thanks Vishesh!

[@SatvikKapoor]

Respected Sir, thank you for your guidance. I have doubt. At 4.26, you are saying that you want to add measure (which ever measure we are dealing with that is "labor" but while typing you are adding "sum of labor cost variable" My doubt is I how do you connect "sum of labor cost variable with Labor selection. When I try to do this with my data, it doesn't give me that option. I have data from excel having values as Forecast Qty and Shipment Qty. Now I used your video and went through all steps mentioned but when i reached Country = var logictest= COUNTROWS(DISTINCT(FILTER(ALLSELECTED(_measure[Selection]),_measure[Selection] ="Country"))) = 1 return if (logictest,[Forecast QTY],blank()). it doesn't allow me and gives me error " the value Forecast Qty cannot be determined . Either Forecast Qty doesn't exist or there is no current row for a column names "Forecast Qty" . I do not know how to link up the _measure vs my data which contains columns such as country, Forecast Qty, Part number ... because of this error, i'm not able to proceed. I have purchased your Udemy course using account kaprishi1 as well. please guide

[@BIElite]

It sounds like the measure that you are usin has no relationship with the dimensions of your visual. If you would like, you can send me screenshots with your formula and data model to my email at [e-mail] and I can take a look!

[@vamshikrishna8143]

Hi parker, I am facing the same issue, it would be a good idea if you can show us if you created a relationship between dimensions. I am very confused.

[@shoeb1359]

@@BIElite Thanks for the awsome trick 👍 One question can we apply this trick on multicard visual. I have to show overall number + number belongs to group + percentage of ticket Any help would be really appreciated

[@mauricioriano1]

Yeahhh, thanks....

[@johns8903]

works but very cumbersome and cluttering when there are dozens of such line charts to be selected or deselected

[@michaelturianytsia7943]

thanks