ฝัง
- เผยแพร่เมื่อ 17 ก.ค. 2018
- In this video, I show you how to dynamically set multiple measures in a chart! You can add or remove these measures simply by selecting them!
Enroll in my introductory or advanced Power BI courses:
training.bielite.com/
Elite Power BI Consulting:
bielite.com/
Data Insights Tools:
www.impktful.com/ - วิทยาศาสตร์และเทคโนโลยี
![[TH] VCT Pacific Stage 2 - Playoffs Lower Bracket Final // PRX vs DRX](http://i.ytimg.com/vi/5HwO1bQyAx4/mqdefault.jpg)
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!
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.
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
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!
Thanks Harrison! I appreciate it!
Really appreciate the simplicity in your tutorials. Thank you for your help!
Glad to hear, Dom! Thanks for watching.
Thanks for the video! It solved my problem to get a calculated cost line to shows in this report!
I have been looking for something like this for a while, thank you
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.
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?
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?
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' ?
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!!
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!
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
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 :)
I have just started with Power BI and this is a great tutorial!
Thanks Supra! Glad you found the channel!
Great video!
Is there a way to display the items on the legend depending on what is selected? Thanks!
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!
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
Thanks for sharing - I am building a viz for MakeoverMonday and this video helped me so much!
Great to hear, Regis!
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!
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!
How do we add /remove labels based on selection? Great video BTW!!!
Awesome application of simple techniques. Very useful. And clearly explained. Keep it coming!
+nboisen Thanks! Always appreciate the support
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.
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?
This was amazing! Super easy to follow too!
Thanks Elwin!
Any solution is there to change legend also dynamically on line chart ? Thank you
The unique value of information. Thank you
Thanks Erol!
Can you share the last video which you mentioned in this tutorial?
We learn Every day. Thank you
Hah yes we do!
Great tutorial, all that in 7 mins GOAT
@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.
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.
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??
I'm going to use this in my work. Thanks for the tip :)
Great to hear, Riaz!
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
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?
Hi good solution ..but how can you control legends in chart?it is still showing respective legend even though you havent selected?
Can this be done for a waterfall chart?
A great tutorial, really liked the step by step instructions. Is there a way to use this with columns (Variables), instead of measures.
I was able to do that trough adding a sum function before the column name
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.
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!
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
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!
@@BIElite ah, that would be advantage
Above steps not worked for me with Direct Query Data. Any workaround to accomplish switching measures for Direct Query ?
Great tutorial! Thank you very much!
that is very clever! I love your video. thank you so much for the tip
really perfect and clear
Very Very Grateful for this!!! 😀
Thanks a lot you saved my life
Great information. Thank you
Appreciate the support as always giavo!
You are a super guru. I have used it in my model and My boss will like for sure.
Haha thank you Kishore.
Can this be done with matrix?
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?
Hi Jonathan, this video is a bit antiquated because of the new Personalized Visuals feature. Video here: th-cam.com/video/eNP36ozsnss/w-d-xo.html
You should be able to do what you are hoping.
Thanks for the great help !! very helpful one!!
Glad to hear!
Good video,
Can I include instead of measure in column in dynamic way
Yep, check out my Dynamic columns videos. There are a couple.
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.
Hey Abdul, I just replied to your comment on my other video here: th-cam.com/video/6jeSIRpjv0M/w-d-xo.html
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
Thanks!!! Works perfect!!
Glad to hear that, Gustavo!
Thanks! it worked!
Thanks for the great video!! Inspiring
And this is probably my second favorite trick lol
@@BIElite 😄 awesome
Thank you for this
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
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.
thanks for solution really helpful.
+Nilesh Chouhan no problem! Thanks for watching
Nice tip.
Great! Do you think this will also be possible in MDX in a multidimensional cube?
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
@@BIElite Denis, if you have these measures in Analysis Services and are surfacing in PBI or Excel, they should be just fine.
Is there a way to dynamically add or remove the legends as well along with the lines based on the slicer selection? Thank you
Any solution is there to change legend also dynamically..!!
Where is the pbix file? I was willing to give up my email for it, too bad
It´s very good and helpful
Thanks Rodrigues!
Great Tip!
Thank you José!
Brilliant 👏 👏 Logic
Can you do this for a table
Check out the new Personalized Visuals feature: th-cam.com/video/eNP36ozsnss/w-d-xo.html
Thanks!
Thanks Eddy!! Didn't see this until now.
Really greatful
Thanks for watching!
you are a genius!!!
Haha thanks so much Walther! Appreciate you watching the video
Amazing
thanks
It is interesting! Thanks!!
Thanks Станислав!
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.
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).
The new Personalized Visuals functionality is your friend: th-cam.com/video/eNP36ozsnss/w-d-xo.html
Thanks
No problem Rita!
Awesome
Thanks Vishesh!
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
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 powerbielite@gmail.com and I can take a look!
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.
@@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
2 years later and I still come to this video for the reference. The legend doesn't respond dynamically now, though
Any solution is there to change legend also dynamically..!!
Yeahhh, thanks....
works but very cumbersome and cluttering when there are dozens of such line charts to be selected or deselected