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

[@Dan-me5li]

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.

[@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) ;)

[@theflyingsword]

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]

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

[@PoemBehera]

The Magic of DAX for Design is amazing.

[@HowtoPowerBI]

Yes! Opens up unlimited options

[@hankoswart8757]

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

[@HowtoPowerBI]

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

[@renekeyzer-andre3242]

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]

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

[@collontomlyn6567]

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

[@HowtoPowerBI]

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

[@Locomaid]

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

[@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.

[@lyonelremond4075]

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]

so nice to hear, thx Lyonel!! 😀

[@sherryparker7205]

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

[@HowtoPowerBI]

Happy to see you liked it Sherry, thanks!

[@kingingway3308]

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

[@nghuwei]

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

[@taleilon511]

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?

[@EliasRudTahi]

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

[@sharkillsbear]

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!

[@jacobfrane6221]

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.

[@__unknown___]

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]

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! 🙏

[@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

[@rupeshads]

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]

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

[@ChrisSmithFW]

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

[@gauravtricks8267]

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

[@jackiefeng7550]

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

[@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!

[@eliasgebru5888]

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

[@HowtoPowerBI]

nice! thanks a lot :)

[@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?

[@mabenba]

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?

[@werneroostendorp1154]

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!

[@konstancyja82]

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?

[@wardhaddad2312]

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.

[@siyandamsibi368]

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

[@BolajiBI]

Creating magic with Power BI 🤯

[@HowtoPowerBI]

thx Bolaji ! ✨💫📊

[@alicedwonderland7733]

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

[@HowtoPowerBI]

Thanks again for watching!

[@samhall100]

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

[@sebastienlove3025]

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 ?

[@Alpha.W-m2h]

Hi Bas, like your help to see if any way to go next-level of this Matrix Gantt chart. For example, we have main project with different sub tasks scheduled by period. can we put all the sub tasks into one row and spread them in the same row by each task timeline, basically to replace the layout with horizontal way. Thanks for your help!!!

[@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?

[@cuarin]

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

[@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

[@benneh4466]

Would it be possible to add subtasks?

[@chelynnes3013]

That was excellent, can't wait to try it

[@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?

[@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

[@patricioolivares5152]

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!

[@smitherbeanz]

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?

[@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

[@jrobinson8898]

Excellent tutorial!! Thank you!!👍🏽

[@HowtoPowerBI]

Thx!

[@haric7519]

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

[@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

[@Ayman154]

godfather of the power bi ,,

[@branzo79]

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

[@eddieeales5699]

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 :)

[@sherinthomas801]

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]

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

[@gileswhalley]

Amazing Bas, thank you!

[@PedroCabraldaCamara]

incredible and amazing Bas. Very well done!

[@HowtoPowerBI]

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

[@PedroCabraldaCamara]

@@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

[@soonyin5835]

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

[@marjorieherrera3031]

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

[@mehdihammadi6145]

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]

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

[@HowtoPowerBI]

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

[@mehdihammadi6145]

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

[@NaimaZhusupova]

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]

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

[@avpr1c]

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

[@HowtoPowerBI]

kzbin.infoMChjMrjgn1Q

[@HowtoPowerBI]

kzbin.info/www/bejne/g5mmZa2gZ8eseLM

[@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

[@Queez92]

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.

[@nikhilpatil2021]

this is awesome thank you

[@ajayvariya4488]

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

[@diegoasm1]

Can it work with hours and minutes in a day?

[@ArpitSharma-kk9tl]

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

[@Entefamilyekm]

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]

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

[@pabeader1941]

@@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]

@pabeader did you find solution for that case?

[@spen2431]

💥💥 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]

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]

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

[@spen2431]

@@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]

^^ 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.

[@gabrielmorais7312]

Very nice, thx Bas.

[@MrSergb]

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 :(

[@diederikvankolfschoten47]

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]

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

[@arunvenkatesh5879]

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]

yea same concept 🙂

[@bharadwaj2008]

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

[@allenmorgan8458]

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]

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]) )

[@julijazas]

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

[@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]

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]

@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.

[@achalbarla120]

Thanks for sharing this

[@HowtoPowerBI]

My pleasure

[@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 :)

[@blackbeerbi]

Existe essa explicação em português?

[@LearnAllways]

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

[@HowtoPowerBI]

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

[@gambu4810]

Brilliant!!!

[@jhazielpetersen761]

Hi Bas, can you make a custom visual that include stacked bar chart and matrix in one?

[@HowtoPowerBI]

Trying to picture what you are trying to build. Like databars in a matrix?

[@jhazielpetersen761]

@@HowtoPowerBI a dupe for inforiver matrix visual. Stacked bar chart with matrix table working seamlessly as 1 visual. Love you Bas, Thanks!

[@adariroopabhavani1067]

it's useful to me

[@viniciusfernandes3552]

Great 👍🏼

[@NgdumlaoEnterprise_Inc]

HOW TO SHOW CURRENT DAY LINE INDICATOR IN MATRIX GANNT CHART

[@angelaandrews4987]

Hi...Just wondering if you ever received a solution to this?

[@camarojay7027]

Seems complicated. Tableau has a food Gantt chart

[@guyinazo]

[@HowtoPowerBI]

😎👊😊