2021 Week 5 | Power BI: Drill Down

10 Comments / Power BI / By

Introduction

Thanks to everyone who participated in the first month of #WorkoutWednesday – Power BI edition. If you are joining us for the first time this week, welcome to week 5! We’ve got a new dataset and a fun new challenge! Last week you learned how to use the powerful drill-through feature. This week we’ll focus on drill down (and up), to provide end-users with control to dig into more granular data.

While we encourage everyone to participate in as many challenges as you’re able, you can start fresh with week 5 – there is no prerequisite for this report!

Requirements

  • We are using a new dataset this week. Please download the starter Power BI Desktop file here. Just a quick reminder that you must be on a PC to user Power BI Desktop.
  • Note that this dataset is not built out to be a proper dimensional model. As we progress throughout the weeks we will incorporate more data modeling.
  • Create 3 card visuals containing the measures: Total Sightings, % in American Southwest, and % Suspected Hoax. Note that these measures already exist in the model.
  • Create a column chart containing Total Sightings over Time (using the Posted field for the shared axis)
  • Create a matrix visual containing the Total Sightings by Location, Shape, and Duration. Turn on the plus/minus icons to enable an alternate method of drilling up and down for your end users.
  • Use the drill-down feature to find the number of Fireball shaped UFO sightings in June 2020 in Las Vegas, NV.

Dataset

This week’s data set uses data from the National UFO Reporting Center. You can access the raw data on their website in the report database. The data is brought into Power BI by date using a custom function. Do not worry – we’ve done this part for you! All you need to do is download the Power BI desktop file and start building visuals!

The dataset contains UFO sightings by Date, Location,  shape, and duration.

When you open the file, you may notice that there is a hidden tab containing resources. Please take a look for details on the source data and to learn more about drilling down (and up) in Power BI.

Share

After you finish your workout, share on Twitter using the hashtags #WOW2021 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd and @dataveld. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year.

Solution

10 thoughts on “2021 Week 5 | Power BI: Drill Down”

  5. Jiaying

    Thanks for the amazing drill down topic and video. I have to use a older version of the Powerbi and was not able to open the workbook. I tried to use the raw data and found out that there are so many files to load. When it is possible, can you give some instruction how you transform the row data into the final dataset in the report? Did you use any modelling technique?

    Thanks again!

  6. Shannon Lindsay

    Hi Jiaying! I did do a bit of data modeling, yes. I connected to the source using web.contents, filtered the data to the last 5 years, and invoked a custom function to pull back data from each date in the ReportDate column. I’m not sure if this will be helpful, but here’s the M code for the data (and I’ll include the custom function below as well):

    let
    // The source step gets data from this URL.
    Source = Web.Page(Web.Contents("http://www.nuforc.org/webreports/ndxpost.html")),
    Data0 = Source{0}[Data],
    ChangedType = Table.TransformColumnTypes(Data0,{{"Reports", type date}, {"Count", Int64.Type}}),
    AddedDateasText = Table.AddColumn(ChangedType, "DateText", each Date.ToText([Reports], "yyMMdd"), type text),
    RenamedReports = Table.RenameColumns(AddedDateasText,{{"Reports", "ReportDate"}}),
    FilteredtoLast5Years = Table.SelectRows(RenamedReports, each Date.IsInPreviousNYears([ReportDate], 5)),
    // The custom function uses the date from the DateText column to pull back reportData from the URL for each date in the ReprotDate column.
    #"Invoked Custom Function" = Table.AddColumn(FilteredtoLast5Years, "reportData", each reportData([DateText])),
    RemovedErrors = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"reportData"}),
    KeptReportData = Table.RemoveColumns(RemovedErrors,{"ReportDate", "Count", "DateText"}),
    ExpandedreportData = Table.ExpandTableColumn(KeptReportData, "reportData", {"Date / Time", "City", "State", "Shape", "Duration", "Summary", "Posted"}, {"Date / Time", "City", "State", "Shape", "Duration", "Summary", "Posted"}),
    ChangedDateTimeType = Table.TransformColumnTypes(ExpandedreportData,{{"Date / Time", type datetime}, {"City", type text}, {"State", type text}, {"Shape", type text}, {"Posted", type date}}),
    RemovedDateTimeErrors = Table.RemoveRowsWithErrors(ChangedDateTimeType, {"Date / Time"}),
    RemoveBlankDates = Table.SelectRows(RemovedDateTimeErrors, each [#"Date / Time"] <> null and [#"Date / Time"] <> ""),
    AddedLocation = Table.AddColumn(RemoveBlankDates, "Location", each if [State] <> "" then [City] & ", " & [State] else [City])
    in
    AddedLocation


    = (reportDate) =>
    let
    Source = Web.Page(Web.Contents(“http://www.nuforc.org/webreports/ndxp” & Text.From(reportDate) & “.html”)),
    Data0 = Source{0}[Data],
    #”Changed Type” = Table.TransformColumnTypes(Data0,{{“Date / Time”, type text}, {“City”, type text}, {“State”, type text}, {“Shape”, type text}, {“Duration”, type text}, {“Summary”, type text}, {“Posted”, type date}})
    in
    #”Changed Type”

  7. GSmith

    I had the same issue (working on Report Server instead of the normal Power BI), and I found I was able to follow the article linked in the description, https://radacad.com/custom-functions-made-easy-in-power-bi-desktop, pretty easily. There’s obviously some variation, but it was pretty cool to learn that, too.
    It was also really interesting to see how Shannon did it differently than I did, once I was able to open the workbook on another computer. Many ways to accomplish the same thing!

  8. David

    Disclaimer:
    Make sure to TURN OF DRILLDOWN before clicking the month of June or your numbers will be way off.

Comments are closed.

Scroll to Top