cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
grijs
Frequent Visitor

Filter on unrelated table

I have two unrelated tables in Power BI:

Table 1: Experiment information

Columns: Experiment name, Start Date, End Date

 

Table 2: Measurements

Columns: Date, Measurement

 

I created a filter on Experiment level, where one of the experiments in Table 1 can be selected. Now I want another visualization that shows the measurements of Table 2, just for that experiment. So the filtering on Table 2 (Date should be between Start Date and End Date of the selected experiment) should be done on the selection of the experiment in Table 1.

 

How can this be done? I am looking for hours now, and don't get it solved...

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft
Microsoft

@grijs

 

What about a calculated table? You can use the two visuals basing on the same calculated table.

 

Table =
FILTER (
    CROSSJOIN ( 'Experiment information', 'Measurements' ),
    Measurements[Date] >= 'Experiment information'[Start Date]
        && Measurements[Date] <= 'Experiment information'[End Date]
)

 

Capture.PNG

View solution in original post

3 REPLIES 3
Eric_Zhang
Microsoft
Microsoft

@grijs

 

What about a calculated table? You can use the two visuals basing on the same calculated table.

 

Table =
FILTER (
    CROSSJOIN ( 'Experiment information', 'Measurements' ),
    Measurements[Date] >= 'Experiment information'[Start Date]
        && Measurements[Date] <= 'Experiment information'[End Date]
)

 

Capture.PNG

View solution in original post

Bitwize_PowerBI
Advocate III
Advocate III

I suppose that for one certain date, you only have 1 and only 1 experiment running. (otherwise you'll never be able to achieve what you want)

I suggest you first create a calculated column (new measure => new column) 'experiment name' in your 'measurements table'.  Use the lookupvalue function to fetch the 'experiment name' from you 'experiment' table.

Then create a relationship between your 2 tables based on 'experiment name'.

Your visualisation for the measurements will now be 'auto-filtered' when selecting one experiment.

 

hope this helps,

Dries

KGrice
Solution Sage
Solution Sage

Hi @grijs. Are you aggregating any of the measurements in your Measurements table? For example, if you have one of your experiments selected and want to show the average of measurements within the start and end date:

 

AvgMeasurement = 
CALCULATE(AVERAGE(Measurements[Measurement]), FILTER(Measurements, Measurements[Date] >= MIN(Experiments[StartDate])), FILTER(Measurements, Measurements[Date] <= MAX(Experiments[EndDate])))

 

If you're wanting to get every value to show individually in a separate table, you can try this. Create a slicer with your Experiment Name, then take your Measurement column and put it in a separate table. Assuming your measurement is a number, it will aggregate by default. Hit the drop-down for it under Values in the table, and select Don't Summarize. This will show all of your measurement values, regardless of which experiment they belong to. However, once you drop the above measure on the table as well, it will filter down to only the measurements that correspond to the experiment selected in the slicer.

 

This will look weird, since you'll have two columns that show the exact same thing. To fix this, adjust the width of either column until it isn't visible.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors