如何在Power BI中基于表格存储的IoT温度数据构建时序折线图

Got it, let's walk through exactly how to create that temperature trend chart with last month's data and week/day drill-down capabilities. Here's a step-by-step breakdown tailored to your IoT dataset:

1. Set Up a Date Hierarchy for Drill-Down #

Since you need to drill from month to week to day, we first need to create a structured date hierarchy using your existing Timestamp column:

• Option 1: Use Power BI's Auto Date/Time (Quickest)

  • Go to File > Options and settings > Options > Data Load
  • Check "Auto date/time" and ensure "New tables" is selected (this creates a hidden date table for your Timestamp column)
  • Back in the Fields pane, expand the auto-generated Timestamp table (it’ll have a calendar icon) – you’ll see pre-built levels like Year, Month, Week, Day, and the full Timestamp. Drag these into a new hierarchy (right-click the table > New Hierarchy) in the order: Year > Month > Week > Day > Timestamp

• Option 2: Manual DAX Calculated Columns (More Control)
  If you prefer to build your own, create these calculated columns in your IoT table:

  Year = YEAR('IoT Data'[Timestamp])
  Month = MONTH('IoT Data'[Timestamp])
  Week Number = WEEKNUM('IoT Data'[Timestamp], 2) // 2 = Monday as first day of week; adjust to 1 for Sunday
  Day = DAY('IoT Data'[Timestamp])
  

  Then right-click your table in the Fields pane > New Hierarchy, name it "Date Hierarchy", and add the columns in order: Year → Month → Week Number → Day → Timestamp

2. Filter to Show Only Last Month's Full Data #

There are two easy ways to restrict the view to last month's records:

• Relative Date Filter (Simplest)

  • Add a Line Chart visual to your report page
  • In the Filters pane on the right, expand the Timestamp column
  • Under "Filter type", select Relative date
  • Choose "Last month" from the dropdowns (it’ll automatically show all records from the previous calendar month)

• Calculated Column for Persistent Filter
  If you want a toggleable filter, create this calculated column:

  Is Last Month = IF(DATEDIFF('IoT Data'[Timestamp], TODAY(), MONTH) = 1, "Yes", "No")
  

  Then add a Slicer visual, drag Is Last Month into it, and select "Yes" to filter to last month's data.

3. Build the Line Chart with Drill-Down #

Now put it all together:

• Drag your Date Hierarchy (from step 1) into the X-axis of the Line Chart
• Drag the temperature column into the Y-axis
• Enable drill-down:
  • Go to the Format tab of the visual (paintbrush icon)
  • Scroll down to Drill down
  • Toggle on "Allow drill down" and "Show drill down icon"
• Test the drill-down:
  • Click the drill-down icon (down arrow) in the top-left of the visual to go from Month → Week → Day → individual 10-second Timestamp records
  • Use the up arrow to drill back up, or right-click any date on the X-axis and select "Drill down to [Level]" for direct navigation

Pro Tips #

• To make week labels more readable, create a calculated column like Week Label = "Week " & 'IoT Data'[Week Number] & " (" & FORMAT(MINX(FILTER('IoT Data', 'IoT Data'[Week Number] = EARLIER('IoT Data'[Week Number])), 'IoT Data'[Timestamp]), "MM/dd") & " - " & FORMAT(MAXX(FILTER('IoT Data', 'IoT Data'[Week Number] = EARLIER('IoT Data'[Week Number])), 'IoT Data'[Timestamp]), "MM/dd") & ")" – this shows the start/end dates of each week
• If your dataset is large, consider enabling Incremental Refresh in Power Query to speed up data loads for future updates

内容的提问来源于stack exchange，提问作者user3301440