Its a little bit unbelievable that you know this trick about conditional formatting (turning the chart into a bar chart and then going back to turning it to a line chart to apply the conditional formatting from the previous chart type) but Microsoft should actually go for an easier solution there. Thanks for the video though, it really helped me out :)!
Thank you Boss!!!!!!!!! 3:38 MAXX 5:16 overallmax 5:52 DAX code with MAXX and MINX 6:30 the trick change from line chart into bar chart 7:00 data colors 9:13 Hex Codes
What i understand is that if you can create a column with desired results in INT format then by applying conditional formatting you can make any colors you want.
First of all thank you very much for this video. I tried this in power BI firstly without adding quarterno and I noticed that minx is taking the min of same quarters of different years (not the min of all quarters), can you please explain purpose of adding quarterno?
thank you! I think it is possible when you overlap two line charts. The normal one and another one that only shows the labels you want to have different (the line itself you could then make invisible). I am gonna give it a try and put it on the list of next videos 🙂
This is awesome! I needed this six months ago and I wish I knew about this trick but hey it's not too late right? Bas thanks for the demo, this just peaked my brain to wonder what other tricks are available that is not explicitly documented.
Great work Bas, I have a question. Why Power BI doesnt allow me in the ALLSELECTED type more than one column. For example I just can select ALLSELECTED( Dim_Calendar[Año]) and thats it...
Hey bas, thanks for the video but what if am interested in having to know high and low by year for example highlighting Q3 red and Q4 Green in 2018 and also highlight Q1 Red and Q3 Green in 2019. how would i do that
Hi Bs! That was truly amazing. I am really enjoying learning Power BI watching your videos. Just one quick question, can we make this dynamic from the measures perspective. Like if we drill up to the year level then we see maximum and minimum for year and when we drill down to quarter level we see maximum and minimum for quarter level? Can we do this? Thanks in advance!
Hi Bass, great video. Could you tell me how to solve a issue with the start and end of the "y" axis with shapes activated? Because the shapes in the visual disappear above or below the axis. And obviously the solution must include using filters, therefore manually assigning the start and end is not ideal. Thank you and keep doing what you doo!!
Hi Matthias, thx a lot! What version are you on - I believe the latest one doesn't do that anymore. Otherwise you could use the conditional formatting option for the scale end (also depends on the version you have if this is an option)
HELP, FOR MY THESIS! I have a "date" column and a "Sales" column. How do I do that without having the "dimDate"? Where does that dimDate comes from and are there any relations I have to establish? I'm writing ALLSELECTED(TableName[Month]), but it gives the maximum value of sales per month, not the overall maximum value, please I need an A in this thesis
you could work with a dummy series that shows the points only that need to be highlighted, set linewidth to 0 .. or you could get there using error bars
Hey Bas ! This technique is awesome to switch between the visuals however I have stuck where you have pass multiple date columns(year,Quarter,Quarter no) in Allselected function which I can't do with the dimdate table. Allselected allows me to pass only one date column i.e either year or quarter. Can you please suggest any solution for this issue.
Hi thanks for the great content What could be the reason it fails? I do manage to make a change of colors in the bar graph But when I change back to a line graph it does not get the color change
oh no 🙁 hmmmm... which pbi version do you have? is it possible for you to share sample workbook? i can have a look if you like (bas.dohmen@datatraining.io)
Hi very useful video and very practical need everyone for every project ......very big thanks to share with us. If it can possible can you plzz provide us excel data file which you used. Very greatful for us...👌👌👌👌👌👌👌👌💐💐💐💐🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂
I know this video is put up a long time ago but i hope someone sees my comment anyway and can answer my question. How do i get it to repeat the identified overall max for, each month, in my case. In my visual it just returns the same value as in total sales column. It correctly shows in the column total but is not repeating it like shown in the video at 3:48. This means when i do my coloring it is all the same color because the value compares to itself so to speak and not the max value
Hi Bas, thanks for the video however I've doubt on this topic I inputted two columns i,e Region and city in allselected dax function and it works fine whenever two dimensions are available in my chart. But when I removed any one of the dimension in my chart my logic was not working. Here I'm expecting despite I remove any dimension still I should achieve this requirement. Hope my question is clear. Thanks!
Ey Sandeep, ah yes I get it. You are right, it only works if both dimensions are in. I think you could check what dimension is showing and adjust what gets returned. Let me give this a try and I will let you know (or put it in a followup video).
I HAVE A QUICK QUESTION: I HAVE CALENDER TABLE I SORTED BASED ON MONTHNO AND WEEKDAY NO, SO I GET DATA FROM JAN- DEC NOW I WANT LINE CHART MONTHLY ON SALES : SO I WROTE BELOW DAX : MAXMINSALESMONTHNO = VAR SALES= [TOTAL SALES] VAR MAXSALES= MAXX(ALLSELECTED('Calendar'[Year],'Calendar'[Quarter], 'Calendar'[Month],'Calendar'[MonthNo],'Calendar'[Weekday],'Calendar'[WeekdayNo]),[TOTAL SALES]) VAR MINSALES= MINX(ALLSELECTED('Calendar'[Year],'Calendar'[Quarter],'Calendar'[Month],'Calendar'[MonthNo],'Calendar'[Weekday],'Calendar'[WeekdayNo]),[TOTAL SALES]) RETURN SWITCH( TRUE(), SALES=MAXSALES,"GREEN", SALES=MINSALES,"RED" ) HOWEVER I AM GETTING GREEN FOR ALL THE COLUMNS. WHAT WOULD BE THE SOUTION.
Its a little bit unbelievable that you know this trick about conditional formatting (turning the chart into a bar chart and then going back to turning it to a line chart to apply the conditional formatting from the previous chart type) but Microsoft should actually go for an easier solution there. Thanks for the video though, it really helped me out :)!
Thank you Boss!!!!!!!!!
3:38 MAXX
5:16 overallmax
5:52 DAX code with MAXX and MINX
6:30 the trick change from line chart into bar chart
7:00 data colors
9:13 Hex Codes
thanks for watching!
Starting with a column chart and later switching to Line chart to highligher markers is a brilliant idea. Hats Off!!!
Sin duda alguna el Mejor en ideas de visualización de Datos en Power Bi en todo el mundo. La explicación 10 de 10. !!!
Gracias Bas!!!
🙏 thanks! Glad you like it
Hey you are one of the awesome teacher in you tube who teaches power bi
Thanks Surendra, glad you like the videos!
The way you explain is exceptional, truly thankful.
Thank you sooooo much :)
Love the background jazzy music
thats good!!! u earned a new subscriber for urself !
kindly do all variation of this which u told in the end ....it would be a great help
awesome THANKS! yes, will do a follow up video 🙂
@@HowtoPowerBI waiting
Thanks alot... exactly what I was searching for.. perfectly demonstrated
What i understand is that if you can create a column with desired results in INT format then by applying conditional formatting you can make any colors you want.
First of all thank you very much for this video. I tried this in power BI firstly without adding quarterno and I noticed that minx is taking the min of same quarters of different years (not the min of all quarters), can you please explain purpose of adding quarterno?
Legendary video! thanks heaps! Just wondering if you could do a demo on where instead of average, its based on a calculated measure ?
Hi, another great video. I would be interested to inow if you could you the same logic to the data labels. Thanks
thank you! I think it is possible when you overlap two line charts. The normal one and another one that only shows the labels you want to have different (the line itself you could then make invisible). I am gonna give it a try and put it on the list of next videos 🙂
THIS WAS REALLY AWESOME!Cannot wait to try it out!
Thank you! 😃 nice to hear you'll give it a try
Great video. Can also use this conditional formatting for the names of the X-axis ? Thanks
This is awesome! I needed this six months ago and I wish I knew about this trick but hey it's not too late right? Bas thanks for the demo, this just peaked my brain to wonder what other tricks are available that is not explicitly documented.
Great work Bas, I have a question. Why Power BI doesnt allow me in the ALLSELECTED type more than one column. For example I just can select ALLSELECTED( Dim_Calendar[Año]) and thats it...
I have the same problem.
¿Pudiste encontrar una solución?
ㅎㅎ 감사합니다!! 콤보차트에서도 적용이 될까요?
Hey bas, thanks for the video but what if am interested in having to know high and low by year for example highlighting Q3 red and Q4 Green in 2018 and also highlight Q1 Red and Q3 Green in 2019. how would i do that
Always great video, question. I would like to highlight my 10 ten customers on a map. Any suggestions ?
Hi Bs! That was truly amazing. I am really enjoying learning Power BI watching your videos. Just one quick question, can we make this dynamic from the measures perspective. Like if we drill up to the year level then we see maximum and minimum for year and when we drill down to quarter level we see maximum and minimum for quarter level? Can we do this? Thanks in advance!
Pure genius. A treat to watch as always 👍😃
Thanks Jamie 😀!
Hi Bas, can we apply the same logic for DATA LABELS?
Awesome video.
Please make a video "how to apply condition formatting on dynamic calculations group".
Thanks God bless you
Thank you! I will put it on the list :)
Super duper... Thanks so much. Great session.
thx again for watching Erica! 😀
Nice video bas. Could you please do a similar one highlighting just the first and last data points. Thanks.
Thanks!!! I’ll add it to my list, in the meantime have a look at this ruclips.net/video/C9aAoadChuk/видео.html
Hi Bass, great video.
Could you tell me how to solve a issue with the start and end of the "y" axis with shapes activated? Because the shapes in the visual disappear above or below the axis.
And obviously the solution must include using filters, therefore manually assigning the start and end is not ideal.
Thank you and keep doing what you doo!!
Hi Matthias, thx a lot! What version are you on - I believe the latest one doesn't do that anymore. Otherwise you could use the conditional formatting option for the scale end (also depends on the version you have if this is an option)
thank you. I was successful.
awesome! happy to hear you got it to work!
your'e good at this 👏👌
as always amazing stuff. Thank you so much
HELP, FOR MY THESIS! I have a "date" column and a "Sales" column. How do I do that without having the "dimDate"? Where does that dimDate comes from and are there any relations I have to establish? I'm writing ALLSELECTED(TableName[Month]), but it gives the maximum value of sales per month, not the overall maximum value, please I need an A in this thesis
is there a workaround for multi lines chart?, this seems to work for single line chart only
you could work with a dummy series that shows the points only that need to be highlighted, set linewidth to 0 .. or you could get there using error bars
Superb Explanation as always !!!
Thanks a lot Mohammad 😊 !
I can't seem to find where there is the ability to do this in the most recent version of Power BI Desktop.
Hey Bas ! This technique is awesome to switch between the visuals however I have stuck where you have pass multiple date columns(year,Quarter,Quarter no) in Allselected function which I can't do with the dimdate table. Allselected allows me to pass only one date column i.e either year or quarter. Can you please suggest any solution for this issue.
Thanks bas!
Hi thanks for the great content
What could be the reason it fails?
I do manage to make a change of colors in the bar graph
But when I change back to a line graph it does not get the color change
oh no 🙁 hmmmm... which pbi version do you have? is it possible for you to share sample workbook? i can have a look if you like (bas.dohmen@datatraining.io)
@@HowtoPowerBI may 2020
I will send you a sample on morning tomorrow tnx
Its connected to ssad tabular with live connection
Hi
very useful video and very practical need everyone for every project ......very big thanks to share with us.
If it can possible can you plzz provide us excel data file which you used.
Very greatful for us...👌👌👌👌👌👌👌👌💐💐💐💐🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂
Ashish, I’m glad it helps! And the download link is in the description :)
Awesome video! Thanks for sharing!
thx Daniel!!!
I know this video is put up a long time ago but i hope someone sees my comment anyway and can answer my question.
How do i get it to repeat the identified overall max for, each month, in my case. In my visual it just returns the same value as in total sales column. It correctly shows in the column total but is not repeating it like shown in the video at 3:48. This means when i do my coloring it is all the same color because the value compares to itself so to speak and not the max value
Thank you very much 😊
Always welcome
How do we highlight the intersection points in line charts in power bi
Great ! Thanks for sharing !!
🙂 happy to hear it was helpful, thx Isidre!
in this can we highlight the data points based on some condition like at least 6 continuous data points above or below Average line?
yes should be possible, you would need to add a further condition to the if statement. Will do a follow up when I find the time 🙂
@@HowtoPowerBI I am not sure about how to find the continuous data points. it would be great if you can suggest something on that.
This process is not working when I put legends. May u please comment on that how to make it work?
Very Nice👌
Thanks Yaser 😊
Can you do the same, but with a barplot with a legend? (each bar will be have differente colors, son color do not help here...(I guess)
How to highlight all the values above confidence interval ?
Hello Bas, where is the excel file used in the video? I am unable to find it in the description
my.datatraining.io/course?courseid=howtopowerbifiles
how to remove dots from each observation?
Super like
Cool
Hi Bas, thanks for the video however I've doubt on this topic I inputted two columns i,e Region and city in allselected dax function and it works fine whenever two dimensions are available in my chart. But when I removed any one of the dimension in my chart my logic was not working. Here I'm expecting despite I remove any dimension still I should achieve this requirement. Hope my question is clear. Thanks!
Ey Sandeep, ah yes I get it. You are right, it only works if both dimensions are in. I think you could check what dimension is showing and adjust what gets returned. Let me give this a try and I will let you know (or put it in a followup video).
@@HowtoPowerBI Thanks Bas will be waiting for your video
But my conditional field is not activated. Kindly help...
CAN WE DO IT WITHOUT CREATING A NEW COLUMN.. AND SINGLE MESURE..
Sure, like in the video
Hi thanks for ur reply..I have the latest version.. could u hint a little bit here.
Bro share the dataset to study and practice it
If possible
Hi there! The links are always in the description:)
@@HowtoPowerBI Thank you I got it
Great solution. But i need to make it work with multiple measures 😫😫
Sibu Min_Max =
VAR MaxOverallSales =
MAXX(
ALL('Calendar'[Month],'Calendar'[Index]),
[Total Sales]
)
VAR MINOverallSales =
MINX(
ALL('Calendar'[Month],'Calendar'[Index]),
[Total Sales]
)
VAR Result =
SWITCH(
TRUE(),
[Total Sales] = MaxOverallSales,"Green",
[Total Sales] = MINOverallSales = "Red",
"Grey"
)
RETURN
Result
I enjoy this video a lot. But that jazz on the background has to go.. pleaasse
Thx Edwin, haha next video more rock&roll 😉😆
I swear every time I watch your videos I say "shut the f*** up... you can do that?!" at least once to the screen.
Hahaha :):) would love to see this
Poorly organized video, could not recreate. I saw other videos that you made which were much better.
Thanks for your feedback
I HAVE A QUICK QUESTION: I HAVE CALENDER TABLE I SORTED BASED ON MONTHNO AND WEEKDAY NO, SO I GET DATA FROM JAN- DEC
NOW I WANT LINE CHART MONTHLY ON SALES : SO I WROTE BELOW DAX :
MAXMINSALESMONTHNO =
VAR SALES= [TOTAL SALES]
VAR MAXSALES= MAXX(ALLSELECTED('Calendar'[Year],'Calendar'[Quarter], 'Calendar'[Month],'Calendar'[MonthNo],'Calendar'[Weekday],'Calendar'[WeekdayNo]),[TOTAL SALES])
VAR MINSALES= MINX(ALLSELECTED('Calendar'[Year],'Calendar'[Quarter],'Calendar'[Month],'Calendar'[MonthNo],'Calendar'[Weekday],'Calendar'[WeekdayNo]),[TOTAL SALES])
RETURN
SWITCH( TRUE(),
SALES=MAXSALES,"GREEN",
SALES=MINSALES,"RED"
)
HOWEVER I AM GETTING GREEN FOR ALL THE COLUMNS.
WHAT WOULD BE THE SOUTION.
Thanks a lot!