Get more out of a MATRIX VISUAL in Power BI | Turning a matrix into a GANTT CHART

Рет қаралды 67,546

Күн бұрын

You want to build a GANTT Chart, but not use any custom visual? In this video I show you how to turn a matrix into a GANTT with the help of some DAX and conditional formatting.
Enjoy the video and subscribe for more Power BI hacks!
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly support@datatraining.io
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
00:00 Intro
01:00 Step 1: Creating a bar chart as a base for the GANTT
01:56 Step 2: Creating a measure to indicate where the bars should be
04:53 Step 3: Apply conditional formatting to show bars
06:54 Step 4: Fixing columns
09:28 Step 5: Applying conditional formatting based on project status
13:23 Step 6: Fixing the tooltip
17:35 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on KZfaq: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI #PowerBI #DataTraining
#powerbidesktop #powerbitraining #powerbideveloper #DAX

Пікірлер: 130

@Locomaid 11 күн бұрын

I knew this had to be possible. You are awesome for sharing. Thanks, Bas!

@ilfacone Жыл бұрын

Bas - I love the way you produce videos on topics that make me think out of the box about what can be done in Power BI. It may look on the surface - That the subject matter is very niche, but in reality, these obscure topics usually produce measures, that can be applicable to more commonly found problems. Thanks for this one, its really cool. (Loved the gem of a shortcut to turn off the auto-sizing of columns) ;)

@sharkillsbear 2 жыл бұрын

Such a great video and breakdown! Thank you so much for posting this. I have several Power BI reports using Gantt charts and I've never liked the custom visuals. I found a resource when I started on the reports that used a matrix but, it was not as well explained and thorough as this video. Subscribed and getting notifications now!

@kingingway3308 2 жыл бұрын

Thank you Bas. I am inspired by your inspiration. You raise the bar with every video.

@theflyingsword 2 жыл бұрын

Thanks a bunch Bas, this couldn't come at a better time, I've been looking for a clean simple solution to create a Gantt chart on PowerBI. Thanks again!

@HowtoPowerBI 2 жыл бұрын

awesome! 👊😀 happy to hear I timed the video well haha

@Dan-me5li 2 жыл бұрын

I’ve pretty much told my team what they were asking for was impossible.. you may have just given me what I need. I’m going to need to highlight the start date and end date different colours so if I can figure out how to get that into the measure I’ll be flying. Thank you. Subbed and liked for appreciation.

@lyonelremond4075 2 жыл бұрын

Another great video Bas!!! I really like your style. You present very well and clearly, making it much easier for anybody to pick this up. Well done, keep up the great work!

@HowtoPowerBI 2 жыл бұрын

so nice to hear, thx Lyonel!! 😀

@renekeyzer-andre3242 2 жыл бұрын

Awesome and very clean video! Great example to show others how to build their Power BI and DAX skills and not just give the whole solution but help other learn how to build their DAX solution! Great job, thanks Bas!

@HowtoPowerBI 2 жыл бұрын

thank you so much Rene, that's really nice to hear! 😀

@BraulioPatriota Жыл бұрын

Many thanks for providing such a well explained video! I was looking for a way to do it in various sites and could not find it.

@chelynnes3013 2 жыл бұрын

That was excellent, can't wait to try it

@ChrisSmithFW Жыл бұрын

You're the best. No doubt about it. As always, thanks so much.

@alicedwonderland7733 2 жыл бұрын

Thank you so much, Bas! Yet another spectacular video!

@HowtoPowerBI 2 жыл бұрын

Thanks again for watching!

@sherryparker7205 2 жыл бұрын

Nice approach. I love the trick for formatting the date column to be narrow. Thank you.

@HowtoPowerBI 2 жыл бұрын

Happy to see you liked it Sherry, thanks!

@jacobfrane6221 2 жыл бұрын

Awesome tutorial! I’m looking for a solution to a variation on the Gantt chart. To use your example, I’d like to show each category as a single line with the projects for each category visualized on that line. So, there would be multiple project bars per category line. I don’t have projects that overlap in each category but that repeat over time. Thanks for putting out such detailed content! Very informative and helpful.

@rupeshads 2 жыл бұрын

Awesome! I like the way you have explained about Gantt Chart in Power BI as I was looking for soming similar in Power BI as I have tried it in Excel with same method, Thank you very much!

@HowtoPowerBI 2 жыл бұрын

great to hear Rupesh! Indeed quite similar to how you could do it in excel as well 😀

@gileswhalley Жыл бұрын

Amazing Bas, thank you!

@hankoswart8757 2 жыл бұрын

Dude, you look like someone I could have a beer with. Thanks for this video, perfect timing for one of my projects.

@HowtoPowerBI 2 жыл бұрын

haha 🍻🍻🍻 just let me know when and where 🍺🍺🍺 😁😉

@mabenba 2 жыл бұрын

Amazing tutorial. I trully love your content!! I have a question, What can we do if we don't have an end date yet for several of the tasks?

@eddieeales5699 2 жыл бұрын

Hey Bas, love the video, very well explained. I've already built something very similar and came here hoping that you had solved how I could only show the Tooltip if I hover over a Matrix cell with a value >=1. Not sure if it's possible but if you have any ideas of where to start I'd be forever grateful :)

@nikhilpatil2021 2 жыл бұрын

this is awesome thank you

@konstancyja82 3 ай бұрын

I have a challenging question, maybe a new video idea? I would like to include in the Gantt chart also the lookout for the project. Project can have several phases: feasibility study, development, scale-up, launch. All those phases have start and end date. It would be wonderful to see them in one line for each project. eg. Mar-April Feasibility study, May-August Development etc... How would you show them in Power BI as a Gantt chart?

@nghuwei 2 жыл бұрын

Thanks for the great content, I’m just wondering if we can create a Gantt chart with group project stage in a single row ? Been looking elsewhere but couldn’t get any good solutions

@debrawildansyah8633 Жыл бұрын

Hi Bas, love your video and your explanation is loud and clear! I have a basic question, so in my Gantt chart, there's a process that will repeat every end of month. I've identified that in my data source, but when imported into the Matrix visual, somehow it only recorded only one week. Do you know why that is and if so, is there a way to make all the other weeks show up in my Matrix chart? Thanks so much!

@smitherbeanz 2 жыл бұрын

Thank you so much for this video, I'm a beginner and this helped a ton with what I'm trying to do! I have some projects that have an end but not a start date, how would I write it so that these projects return an 0?

@jackiefeng7550 11 ай бұрын

That's awesome tutorial! wandering if we can fix column days as calendar days of each month, and show event bookings across years months with filter? thanks

@gabrielmorais7312 Жыл бұрын

Very nice, thx Bas.

@eliasgebru5888 Жыл бұрын

It is great as always! Many thanks! I am a citizen developer and really appreciated it!

@HowtoPowerBI Жыл бұрын

nice! thanks a lot :)

@user-cd1jo5cy7k Жыл бұрын

Amazing tutorial. I trully love your content!! I have a question, CAN YOU DO IT FOR HOURS ALSO AND DATES?!!

@sherinthomas801 2 жыл бұрын

Hey Bas, another fantastic video! Although I was aware of some of the tricks here but you took it up a notch. Keep it up. I was wondering if you could do a video on incremental refresh when combining files from a sharepoint site?

@HowtoPowerBI 2 жыл бұрын

Thank you Sherin!!! 😀 Sounds interesting, let me look into it!

@jrobinson8898 2 жыл бұрын

Excellent tutorial!! Thank you!!👍🏽

@HowtoPowerBI Жыл бұрын

Thx!

@siyandamsibi368 Жыл бұрын

Hey Bas, great video! Would the same approach work when working with time instead of dates?

@patricioolivares5152 10 ай бұрын

Hi Bas! Thanks a lot for the helpful video. This is a nice workaround to avoid custom visuals. Is there any chance that you can go one step further and teach us how to create a Gantt Chart using SVG objects in a measure and the matrix visual? I saw a snapshot of a table that includes 4 columns for dates: baseline and current dates. Any advise will be much appreciated!

@MaziarShodjaziba Жыл бұрын

Hey Bas! Great video as always, love your stuff! 😊 I've been together with a colleague trying to create a Gantt Chart for vacation. The principle is of course exactly the same like the example you show. However, we have a weird bug/issue. When we add the date hierarchy, as we need to, since we want to see the year, month and day, we only get a "1" value for the first day of each period for the vacation. Kind of like you have the year, week and day. So for a project that starts for instance in September, and ends in mid October, we only see values for all the days in September, and nothing shows for October. I've been wondering how to get around that issue and if anyone has faced something simillar? 😃 Thanks

@amolupale2240 Жыл бұрын

This is awesome!! thank you so much.... I do have a question, is there a way to add data labels to the bars, e.g. adding completion date to the bars

@PedroCabraldaCamara 2 жыл бұрын

incredible and amazing Bas. Very well done!

@HowtoPowerBI 2 жыл бұрын

thank you for watching so many of my videos and always leaving a comment, appreciate it Pedro!

@PedroCabraldaCamara 2 жыл бұрын

@@HowtoPowerBI believe me it's all my pleasure and this place is a must for whom want to learn power bi without wasting to much time. Thank you so much for being there

@taleilon511 2 жыл бұрын

Dear Bas! Thanks a LOT for this tutorial! It is absolutely AMAZING! I was wondering, can we add here the dotted blue line for today's date, like you did in the bar chart Gantt?

@angelaandrews4987 Жыл бұрын

wondering if you ever learned of a strategy for this?

@bradlindemann5839 Жыл бұрын

Hey Bas, great video! I have a separate table with offline weekend start & stop times. How can I add offline weekend days in a different color?

@gambu4810 2 жыл бұрын

Brilliant!!!

@gauravtricks8267 Жыл бұрын

Hii i love your teaching style and your tutorials are really helpful. Can you tell me how you edit your videos 🙏🏻🙏🏻🙏🏻🙏🏻 please reply

@PoemBehera 2 жыл бұрын

The Magic of DAX for Design is amazing.

@HowtoPowerBI 2 жыл бұрын

Yes! Opens up unlimited options

@MrAbsolute13 Жыл бұрын

Hello. Thank you for your videos. Really helpful. Can we have the sum of these 1s per column at the bottom of each column and how please?

@werneroostendorp1154 2 жыл бұрын

Hi Bas, thanks for the video! When I set a project on 3th of January 2022, it returns week 2 instead of week 1, do you know what I am doing wrong? Thanks in advance!

@sebastienlove3025 8 ай бұрын

Nice Video. Question : is there a way to merge the date header cells so that the month number appears only once in one large cell ?

@BernardoNunes7 Жыл бұрын

Thank you for the tips, I the project category formating, is taking the earliest finished project data when we close the expanding "+" any reason why? And like this give a wrong perspective when the table is minimized. Thanks for the help

@achalbarla120 4 ай бұрын

Thanks for sharing this

@HowtoPowerBI 4 ай бұрын

My pleasure

@__unknown___ 7 ай бұрын

For anyone wondering how to have more than one sequence of dates in the same line (ie, more than one start/end date per project): 1) in a new column called "DateRange", create a list of all the dates between start and end and expand it (there are videos on youtube teaching how to create a list from a range of dates) 2) create a measure that returns 1 when dimDate equals DateRange or 0 otherwise It would be something like this: CF Gantt = IF ( HASONEVALUE(dimDate[DateRange]), IF ( VALUES(dimDate[Date]) = MAX(Project[Date]), 1, 0 ), BLANK() ) the rest is the same

@yoluli88 6 ай бұрын

Hi! I'm facing the same issue, I need more than one start per project. I tried adding the DateRange by crating a list in M, but I think it should be in the Project table instead of the dimDate table (as start and end are only on the Project table). Could you explain a liitle more about your solution? It would really help me a lot! Thanks! 🙏

@varunpeter9512 Жыл бұрын

I was looking at your dimDate table and how to show that with Fiscal Year as month number 1? For example, April = Month 1

@collontomlyn6567 2 жыл бұрын

The trick w/ the column header size is absolutely BRILLIANT, just saying.

@HowtoPowerBI 2 жыл бұрын

haha thx 😄 let's hope we can just put a number for columnwidth in the future though!

@cuarin 2 жыл бұрын

My project dates between oct 2020 to 0ct 2021 and after adding conditional formatting dax, it shows only activities for 2020 , how to do.

@BolajiBI 2 жыл бұрын

Creating magic with Power BI 🤯

@HowtoPowerBI 2 жыл бұрын

thx Bolaji ! ✨💫📊

@viniciusfernandes3552 2 жыл бұрын

Great 👍🏼

@marjorieherrera3031 4 ай бұрын

Thanks for this video!!! My gantt chart looks gorgeous. Looking for a way to grey out the weekends, anyone did the same?

@adariroopabhavani1067 2 жыл бұрын

it's useful to me

@wardhaddad2312 3 ай бұрын

Hey, first of all, thank you so much for the video. Secondly, is there a possible way, to use conditional formatting, in a way, where every blank is filled with a different color? I have created a matrix, in which there are spaces between dates in all projects, and I want to fill between these dates with a color, that is different for every row.

@branzo79 Жыл бұрын

Man this helped me today. Except, that the tooltip doesn't work after the current year. :(

@royalh13 Жыл бұрын

Unfortunately, this method only works when the min start date and max end date are within 100 days of each other, since the Matrix visual only allows for 100 columns. To get it to display periods outside that 100 day range, you have to filter the dates to a different 100 day range. This means that building a chart that covers an entire year (Jan 1 through Dec 31) isn't possible, unless you want to filter for blocks of 100 days...

@branzo79 Жыл бұрын

Yes...any idea how to fix this? Tooltip works, but not for dates that then blend into the next year...:(

@muzinkuhlu2592 Жыл бұрын

Also the displayed Gnatt Chart Range is not 100% accurate when collapsing to Month view instead of day view

@diederikvankolfschoten47 2 жыл бұрын

Hi Bas, really like your video's! 1 remark, I noticed you use the system 2 argument for the weeknum function. This returns the wrong weeknumber for some years because it doesn't take the European ISO weeknumbering standard into consideration. It's better to use system 21 like we have in Excel. This is an undocumented feature which I recently discovered in the MS documentation updated in june of this year. If you use weeknum, 2 and weeknum, 21 in the same calendar you wil notice a difference in weeknumbers of 1. I try to teach this in my Excel and Power BI courses. Hope this helps.

@HowtoPowerBI 2 жыл бұрын

thx Diederik! 😀 indeed, for EU - return type 21 is usually the one used

@Ayman154 Жыл бұрын

godfather of the power bi ,,

@julijazas 2 жыл бұрын

How to add a label in right of each "bar" t oshow a completion %? and how to add a constant line with today?

@mahadevshah3099 Жыл бұрын

any date between project start and end date can be considered as milestone/go live date & is there way we can highlight this date

@jasonhawthorne2876 Жыл бұрын

Looks promising. Could you share the fields in your custom date table and whatever relationships you've created on the back end? I've watched two of your videos now and neither shows or explains this table. Both seem completely dependent on the date table but provide no explanation. I'm not sure how everyone else commenting knew how to set this up. I continue to get the error "syntax for ')' is incorrect and although I've created the date, weeknum, etc fields it doesn't work. I notice the visible image / hierarchy for the data table doesn't match mine. I appreciate any help.

@darinflamandita7917 Жыл бұрын

Same problem! Could anyone you please share on how to create that dimDate table?

@arunvenkatesh5879 2 жыл бұрын

Thanks for the video. It was really helpful. Please let me know whether this matrix model will work if start date and end date are date time values and I want the data at an hour level. Purpose is to know number of people are online at that hour.

@HowtoPowerBI 2 жыл бұрын

yea same concept 🙂

@mehdihammadi6145 2 жыл бұрын

Thanks for sharing BAS, this is a really good one but as unfortunately the Matrix visual is limited to 100 columns so with a day granularity, we can just show 100 days. By the way, the columns width trick is incredible

@edwinmv1610 2 жыл бұрын

But you could make it 100 weeks right? or 100 years?

@HowtoPowerBI 2 жыл бұрын

that's right should have mentioned that. Maybe combining it with a date slicer would help?

@mehdihammadi6145 2 жыл бұрын

@@edwinmv1610 Yes for sure and we can also add slicers. I'm just frustrated to get that limit

@MrSergb 2 жыл бұрын

Hi Bas, fantastic video, thanks a lot! Does this solution works with time slicer? When I add slicer from date table it does not filter the Gantt table and it shows still all dates as of first starting date, what I do wrong :(?

@marjorieherrera3031 Жыл бұрын

Did you manage to find a solution? I added a time slicer from my "dimdate" table and it does filter the gantt but the results are not accurate :(

@spen2431 2 жыл бұрын

💥💥 Always love your videos and always pick up something new - so THANK YOU 👍. One question theough..... why? Why would you do this over using the custom Gantt visual. I know you said you may not want to... but really can not think of a reason not to use the MS Gantt visual.

@lyonelremond4075 2 жыл бұрын

Good question. Not everybody wants to use custom visuals. Many companies ban the use of custom visuals as they see it as a security risks. Others allow only "certified" visuals. But also, this is a good example of what can be achieved with a little creativity, thinking outside the box :)

@HowtoPowerBI 2 жыл бұрын

couldn't have said it better 😄 thx Spen for the question and thx Lyonel for answering

@spen2431 2 жыл бұрын

@@HowtoPowerBI Hi Bas. I have another related question. I have a project that is due to span over 6 months. I have a total of 30 days. I want to be able to visualise 5 days per month in a Gantt- or bar.. 👍. (also assume many projects, all with varying project duration/start dates and project timespan). I'll grab a cuppa and have a think myself. Thanks in advance.

@spen2431 2 жыл бұрын

^^ Plus - There could be a project that has 200 days allocated over 2 months. (so 100 "days" per month) I am not bothered about the number of working days in a month.

@Entefamilyekm 2 жыл бұрын

Hello. Thanks a lot for making wonderful and super helpful videos. I have a question - Is it possible to make a Gantt chart in matrix visual for a single project with multiple timelines?

@HowtoPowerBI 2 жыл бұрын

thx for watching Vivek! what do you mean exactly with multiple timelines - could you elaborate what you mean

@pabeader1941 2 жыл бұрын

@@HowtoPowerBI I bet it's something like Start at 7:00 am run til 9:00am. Then start again at 3:00 pm and run til 6:15pm. There are multiple starts and stops for the same project line.

@Pavlushok 2 жыл бұрын

@pabeader did you find solution for that case?

@haric7519 2 жыл бұрын

Hi, We have Projects with multiple Tasks. Users need a chart showing each Project duration based on Start and End date with Actual start and finish Dates. its Same for Task's also. How we can achieve this. Please help me here. Thanks in Advance, Hari

@soonyin5835 11 ай бұрын

Hi, i would like to show whole year date instead of the duration date. May i know how to change on the measurement?

@allenmorgan8458 2 жыл бұрын

Hey Bas! Thanks for another great video. I am trying to do something very similar.. but I am having issues. The problem I have is that I have a "TIME" table with 12 hour time table. I have a "CALLS" table with start and end times (durations) that I am trying to add into the same sort of matrix/gant. The problem is the durations between the start/end date .. and well as the end date.. dont show in the proper "bucket". I am kind of stuck on this one and have tried everything. Any suggestions on this? working with durations (HH:MM:SS) seems to be fairly difficult for me. I am basically trying to show, through the work day, if specific agents were on the phones or not throughout the whole shift.

@HowtoPowerBI 2 жыл бұрын

you need to wrap the times in a time value function I think then it will work 🙂--> cf = VAR StartTime = CALCULATE( MIN(Calls[Start]), REMOVEFILTERS(TimeTable) ) VAR EndTime = CALCULATE( MIN(Calls[End]), REMOVEFILTERS(TimeTable) ) VAR CallTime = TIMEVALUE( MIN(TimeTable[Time]) ) >= TIMEVALUE( StartTime ) && TIMEVALUE( MIN(TimeTable[Time]) )

@samhall100 Жыл бұрын

We're going to use this method to create a matrix that shows when employees are off on holidays!

@user-vd8cd1cq5e 6 ай бұрын

Hi! Thanks for the video. small question: how to show the project Start and End date on the roadmap? because for now it shows only Start Date

@HowtoPowerBI 6 ай бұрын

not sure what you mean? it shows the start + end date

@LearnAllways 2 жыл бұрын

HI boss, Could you be able to do a video for using Time in Bar Chard, based on call centre data

@HowtoPowerBI 2 жыл бұрын

thx for the Idea Raj! what exactly would the bar chart show? could you explain more?

@Queez92 6 ай бұрын

Is there any way to make it show the results of a calculation instead of a "1"? I'd like for it to show how many man-hours we need to deploy/produce per week so I'd like to show that in each square. For example, if we need to use 1000 hours over 10 weeks (= 100 hours per week) then I know I'd to send more than 1 person to get the job done. When I try to replace the "1" then it only shows the calculation in the start date week.

@ArpitSharma-kk9tl 2 жыл бұрын

I want to show timeline even on the minutes, Here you show them only in days. Do you have any suggestion for that

@bharadwaj2008 2 жыл бұрын

is there a possibilty to display all the data without being able to scroll horizontally and vertically.

@diegoasm1 2 жыл бұрын

Can it work with hours and minutes in a day?

@blackbeerbi 3 ай бұрын

Existe essa explicação em português?

@superlaffin39 Жыл бұрын

Hey Bas, can I ask for that hoodie you're wearing. I so love it. It would be great if you can send me one :)

@ajayvariya4488 6 ай бұрын

What if the project lasted for just one days.... Such project is missing from the Gantt chart. Could you please please advise. I'm building a report to track which all users are out of office or on vacation. Based on current logic users who is out of an office for just one day, is missing from the gantt chart. @how to Power BI

@avpr1c 8 ай бұрын

Did you cover how the "dimDate" table was created or the data structure within it?

@HowtoPowerBI 8 ай бұрын

kzfaq.infoMChjMrjgn1Q

@HowtoPowerBI 8 ай бұрын

kzfaq.info/get/bejne/g85zZ92bl8jdcoU.html

@camel4o Жыл бұрын

First thing -THANK YOU for all your work. It is brilliant! Second thing - if you can spare 2 minutes to just let me know HOW you can insert values in the project row if it doesn't match the criteria. For example ....if the project starts on 1.01.2022 and ends on 01.05.2022 I will see 1 in the Matrix and I can achieve that...however, I WANNA also see something after 01.05.2022, let's say 100. However, every time when I use false statement to include that the matrix automatically provides ALL projects and not the projects that should be filtered in the first place :(

@VanessaSuan 10 ай бұрын

Hi, did you resolve this issue? I have the same issue, I'm making it for just one project and when I add the "CF Gantt" show now other projects that I don't want to show.

@srijakodali5318 8 ай бұрын

@HowtoPowerBI Even I am facing the same issue for my project. I am able to see 1's from 10/01/2023 and for all the dates till 10/05/2023. But if the next start and end dates for the same row value are 10/08/2023 till 10/15/2023 - I am not able to see 1's for it. Can you please suggest the DAX commands to use in measure. Let's consider there being multiple start and end dates and we want to see all of them on the same row with 1's.