sqlpowerbirelationshipdirectquery

Use both an Inactive AND Active relationship in the same measure

I have a model imaged below. Let me explain the model. Note: All connections are SQL connections using Microsoft SQL.

Buffer_Data is my fact table. Buffer_Data is a log of everything from customer interaction to maintenance to tripping of a tipping sensor in a machine. It does not contain a link directly to the property table. It connects to the mast table on 2 columns, a machine ID and a MastID. These are concatenated fields for a 1 column relationship. It is a direct query because it contains close to 1 billion records.

Mast is the machine table. It is a Type 2 slowly changing dimension table. It has a location string which is super specific to the point we can identify exactly where in the building the machine is. This is parsed by a location_ID/Location_Code field and joined to the location filters. Also, because it is type 2, we have an "Audit Date" table which tells me which version (or record) was active on a given date. This is a direct query as there are several million records.

AuditDate is as described above. For each machine locationstring, there is a record for every day the machine was active. It identifies the "version" number that was active on that day. A machine has different versions because 1 day it may have a set of configurations, and on another day we may change those configurations. This too is a direct query as it contains several hundred million records.

Location_Filters is an imported table. It contains a distinct list of location strings, which can be tied to a property. It contains tens of thousands of rows.

Property_Table is an imported table, contains 20+ rows.

Date table is an imported table, only containing 1 column of dates listed as datetime. It is connected to the Buffer_Data on an inactive relationship to a datetime column within Buffer_Data.

As you can see by the connections below, I have connected my DateTable 2x to the Buffer_Data causing one of the relationships to be inactive. To be clear, I understand WHY this is the case. My question is, how do I override and force PBI to use both relationships at the same time.

REASON:

As I previously stated, the Mast table has multiple versions of the same machine. If I do NOT use audit date, the number of records that may return in the Mast table is a couple of hundred records, which then have to filter Buffer_Data. Using the Audit Date table, I can limit this to less than 10 records, greatly increasing the efficiency of the query. However, this ONLY filters the Buffer_Data table on the specific machine and will return data for the machine for ALL time. I ALSO want to limit the time frame by the date selected, hence the connection from Date_Table to Buffer_Table on 2 values.

THINGS I'VE TRIED:

Removing Audit Date table. Without Audit Date, my Date_Table only connects to Buffer_Data on 1 column. Results come through as expected. However, because the Mast table is trying to filter Buffer_Data from a couple of hundred records, the resulting query is extremely slow and ultimately not really user-friendly.

No Direct Query. I have created a separate file with no direct query. All data is imported into the file. I was able to restrict the incoming data by combining M-Code with SQL query language. Essentially, the process is for the end user to edit an Excel spreadsheet. Refreshing the data modifies the SQL query, limiting the amount of data coming in. While this is currently working, it is not something that can be published, as multiple people need to use this report. Someone modifying the spreadsheet and refreshing the data while someone else is working causes issues. We have resorted to using individual desktop files, but the training overhead is huge, teaching people how to properly modify the file for their machine.

Creating a Reference Table in PowerQuery of Buffer Data. My thought process was, this is a "reference Table" and since it is direct query, the reference table would essentially contain the "machine filter information" from slicers and then the reference table would be filtered by the date. However, it just returned the information for all machines on that day. It didn't work.

Creating a 2nd date table to filter Buffer Data down. This would mean I need 2 date slicers on the file. While certainly much easier than the Excel process, I found it still wasn't working 100% as expected. The query that is generated tends to be slow.

Creating a measure with 2 USERELATIONSHIPS. I thought if I used USERELATIONSHIPS 2x in the CALCULATE measure, this might work. I did not receive an error when completing the measure, but when putting into a visual, I get the error that there is a locking conflict. So, this didn't work.

EXPECTED RESULTS:

Again, I fully understand WHY PBI doesn't like or even want to have 2 active relationships on 1 table to another. But in this case, I'm looking for any method that may work in being able to make them both active at the same time. I also understand the potential that this may return a blank dataset if it causes conflict in the filters. But the way old SQL queries were set up, they would have done the same. So in this particular case, that is ok.

Is there a way to force active and inactive relationships to work together when using a hybrid model like imaged below?

Model Image

Solution

  • First, to answer your question, you cannot activate these relationships in the same measure because they create a circular reference which is why you encountered the locking conflict.

    Second, there are a lot of issues with the model:

    After 1 billion rows are added, it gets hard to start changing the model around, so here's my best suggestion. You could try writing a query that joins Buffer_data, mast and audit together and use that as the one direct query table in the model. The rest of the tables are imported and join to that.

    BTW, love them handle, McFly :)