Forum Discussion
Helper IIIUnrelated Tables Question
I have the following 2 tables that are currently unrelated.
Table 1
Run Start Run End Run ID
Table 2
Eventtime Reject Count
What I'd like to do is compare the Eventtime in Table2 to see if it falls between the Run Start and Run End Date/Times in Table 1. If it does, then the Run ID is returned in a calculated column in TAble 2. I want to do this so that I can then link the two tables using Run ID.
Is this the correct way to do this? Is there another option I'm not thinking of?
7 Replies
vicky_Super User
You'll need to rethink your approach - even if you get the RunID to work, you will probably get a circular dependency error when trying to create relationships / do calculations. See https://www.sqlbi.com/articles/understanding-circular-dependencies/
cbruhn42Hmmm, any ideas? I was worried about the circular dependecy issue.
- cbruhn42
Would it work to merge the two tables and then create a new column with the logic of comparing the dates?
- Ashish_Mathur
Hi,
This can be done in the Query Editor. Share some data to work with.
Whatever approach you take, if table 1 has multiple rows with overlapping Run Start/Run End times you would be in danger of potentially creating unwanted duplicate rows by linking up to table 2 using only a date/time field.
If this is not an issue, you may want to consider linking the tables outside of Power BI in a view (depending on the data source) and then pulling the view into the report.
- cbruhn42
Could I summarize the two tables and do a date/time comparison within the summarize? The two tables do not have overlapping start/stop times.
ExcelMonkeImpactful Individual
Just adding two cents here:
Whilst Power BI is a great tool for larger data sets, it sounds like Excel may be the better tool to solve this problem. I would then use the Excel spreadsheet as a database to create any visuals Power BI may offer.
