Advanced filter based on variable and multiple tables

[Tie-Tie]

Hello,

I'm very new to PowerBi and would like to explore the possibilities with it.

I'm working on a small project where I created multiple weather stations equipped with sensors. 

All data is stored into the following overview:

• Raw Data: Contains raw sensor data (time series) sent by the sensors
• Sensor: Table with overview of all sensors being used (e.g. DHT11, a Humidity sensor)
• WeatherStation: A small composition of sensors and housing which are located in different places
• StationSensorMap: mapping between weather stations and sensors; shows which sensors are installed on the weather station
• Forecast: I wrote a simple script that performs a forcast based on the latest values received in the raw sensor table. The result per station is stored in the table

What I've done so far in a report:

• Graph that shows forcasting (graph 1)
• Graph that shows all raw data (graph 2)
• Slicer containing all sensors to filter graph 2
• Slicer containing time to filter graph 1 & graph 2 (therefore I created a TimeTable table to control both graphs)

I added next a functionality to drillthrough from graph 1 by using the forcasting (returns WeatherStationName)

Now I want to fill the second report with the same graphs. The forecasting curve went easily. How could I filter the slicer containing sensors to only a selection of sensors that are equiped on the Weather Station? Graph 2 has to show the raw values of sensors inside this list (which can be filtered by the user as well).

Concrete actions should be: 

1. Get the selected value (got from research that using a Measure is the way to do this; I'm able to do this
    - Contains name of weather station (e.g. Houston1)
2. Get the StationID of corresponding name
3. Look-up all SensorIDs in StationSensorMap linked to the StationID
4. Get all SensorNames linked to the SensorID in Sensor and fill those values in the slider
5. Show all raw data related to the sensors mentioned in the slicer

So I'm a bit stuck as of step 2. Am I still correct that I should use DAX to get this done? If so, would you have any idea on how to do this exactly?

Thanks in advance!

[v-yuta-msft]

Hi Tie-Tie,

You should build a relationship based on StationID column from table WeatherStation to table StationSensorMap, then you can use slicer or DAX function measure like related to filter StationID in StationSensorMap.

In addtion, make sure there's no duplicate values in StationID column.

Hope this can help.

Jimmy Tao

[Tie-Tie]

Hi Jimmy,

Thanks for your reply.

the relationship you proposed would be a many-to-many relationship, so I guess this will not work out?

The image above shows all one-to-many relationships

Best,

Tie-Tie