Month: July 2019

Create A Gantt Chart In Power BI With A Matrix Visual

Posted on by Annie D

Managing projects effectively requires clear visuals that communicate timelines and task progress at a glance. While Power BI offers many visuals, building a Gantt chart using the built-in Matrix visual is a powerful technique for showing detailed project timelines without needing custom visuals. This blog post walks through the key concepts and steps for creating a Power BI matrix Gantt chart, perfect for project managers and data analysts alike.

What is a Matrix Gantt Chart in Power BI?

A Gantt chart visually represents project schedules by showing tasks as bars mapped over calendar dates. Unlike using custom Gantt chart visuals, you can cleverly use Power BI’s matrix visual combined with DAX formulas and conditional formatting to mimic a Gantt chart. This approach is advantageous because it leverages native visuals without extra installation, is highly customizable, and integrates seamlessly with your existing data models and filters.

Why Use a Matrix Gantt Chart?

  • Native Power BI Visual: No need for third-party downloads or licensing.
  • Dynamic and Interactive: Integrates with slicers and filters for dynamic timeline updates.
  • Detailed Task View: Offers drill-down capabilities across project hierarchies.
  • Full Control Over Design: Customize colors and styles through conditional formatting rules.

Step-by-Step Guide to Creating a Power BI Matrix Gantt Chart

  1. Prepare Your Data Model
  2. Your dataset should include at least these columns:
    • Project or Task Name
    • Start Date
    • End Date or Duration(Optional)
    • Task Status or Category
    • You also need a Date table in your data model to serve as a timeline backbone.
  3. Establish Relationship sConnect your Date table to your Project table using the Start Date (and possibly End Date) fields to enable time intelligence across visuals.
  4. Create a Matrix Visual
    • Place Project Name (and subcategories if needed) in the Rows.
    • Place Date from the Date table in the Columns.
  5. Create a DAX Measure to Highlight Task DurationWrite a measure that returns 1 if a given date falls within the task start and end dates and 0 otherwise. This will be the basis for your Gantt bars.Example simplified logic: TaskActive = IF( SELECTEDVALUE('Date'[Date]) >= MIN('Projects'[StartDate]) && SELECTEDVALUE('Date'[Date]) <= MAX('Projects'[EndDate]), 1, 0 )
  6. Apply Conditional Formatting Use conditional formatting on the Matrix’s Values field. Format the background color to:
    • Show a distinct color (e.g., gold or blue) when the TaskActive measure equals 1. Show a lighter or neutral color when 0.
    This creates the visual bars of the Gantt chart within the matrix cells.
  7. Enhance Your Visual
    • Turn off subtotals for clarity.
    • Use slicers to filter projects or timelines dynamically.
    • Add tooltips with task details.
    • Customize colors by task status or category using additional measures and formatting rules.

Benefits of Using a Matrix Gantt Chart in Power BI

  • Cost-effective: No licensing or external visuals needed.
  • Integrated experience: Aligns with your Power BI reports and dashboards perfectly.
  • Scalable: Works well from small projects to complex multi-phase portfolios.
  • Customizable: You control colors, interactivity, and granularity.

By leveraging Power BI’s matrix visual combined with smart DAX measures and conditional formatting, you can build a robust, customizable Gantt chart tailored to your project management reporting needs. Start experimenting with your project data today and unlock richer timeline insights right inside Power BI!

Other Options/Resources

For those interested in alternative approaches, there are also custom Gantt chart visuals available in Power BI Marketplace, but the matrix method provides unmatched flexibility and control for many project reporting scenarios.

Delete Rows Based on a Cell Value (or Condition) in Excel [With and Without VBA]

Posted on by Annie D

Managing data efficiently in Excel often means removing unwanted rows that meet certain criteria—such as rows with specific text, dates, numbers, or even partial matches. Whether you’re cleaning up a small spreadsheet or preparing a large dataset for analysis, understanding how to delete rows based on cell values will help you keep your data tidy and relevant. This guide explores multiple techniques to remove rows in Excel according to specific cell values, making your data management tasks faster and more accurate regardless of your proficiency with Excel features or VBA automation.

This tutorial explains several methods to remove rows from an Excel worksheet based on the value in a specific cell or according to set conditions.

Here are four different approaches you can use to delete rows depending on their cell values:

  1. Using Filters:
    Apply a filter to your data, select the criteria you want to remove (for example, rows where “Status” is “Inactive”), and delete all the filtered rows at once.
  2. Sorting Data:
    Sort your data by the column you want to filter (e.g., sort by “Department” so all “Sales” records are grouped together) and then delete all the matching rows in one go.
  3. Finding Cells with Specific Values:
    Use Excel’s “Find” feature to locate cells with a value like “Expired”, select those rows, and delete them all together.
  4. VBA Automation:
    Automate row deletion by using a VBA macro that filters and deletes based on your criteria (e.g., remove all rows where “Order Status” is “Cancelled”).

Tip:
Choose the method that best fits your dataset’s structure and your workflow. Remember, deleting a row removes everything in that row—including all data to the left and right. If you only want to clear certain cells but keep the row and other information, consider using the filter and dummy column trick, or manually clearing cell contents instead of deleting the row.

Wildcard Matching Example:
With Find and Replace, you can use wildcards for powerful matching. For instance, to find every region ending with “East” (such as “North-East” or “South-East”), type “*-East” (the asterisk stands for any sequence of characters).

Example VBA Codes

Delete All Rows Where “Status” is “Inactive”:

textSub DeleteRowsWhereInactive()
    ActiveCell.AutoFilter Field:=3, Criteria1:="Inactive" 'Assumes the Status column is column 3
    ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete
End Sub

Prompt User before Deleting Rows Where “Order Status” is “Cancelled” Without Deleting the Entire Row:

textSub DeleteCancelledStatusCells()
    Dim MsgboxAns As Integer
    ActiveCell.AutoFilter Field:=4, Criteria1:="Cancelled" 'Assumes Order Status is column 4
    MsgboxAns = MsgBox("Are you sure you want to delete these cells?", vbYesNo + vbQuestion)
    If MsgboxAns = vbYes Then
        ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)
    End If
End Sub

Delete Rows in an Excel Table Where “Department” is “Support”:

textSub DeleteRowsinTableForDepartment()
    Dim Tbl As ListObject
    Set Tbl = ActiveSheet.ListObjects(1)
    ActiveCell.AutoFilter Field:=2, Criteria1:="Support" 'Assumes Department is column 2
    Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
End Sub

**#Excel #ExcelTips #A #ExcelTutorial

Let me know if you need more tailored code or examples for your data!