on 25 Nov 2015 01:19:49
Need the ability to create multiple relationships between table pairs. Example: Dynamics GP - SOP Sales Transactions and Sales Line Items have to be joined by SOP Type AND by SOP Number.
- Comments (17)
- Merged Idea (2)
RE: Add the ability for multiple relationships between table pairs
Quite shocking that almost eight years (April 2023) since the idea was submitted, Power BI is still not capable of joining two tables using a complex join (BEWEEN, AND, etc.). Why can't plain ANSI92 SQL be used for linking tables which is common in this industry?
RE: Add the ability for multiple relationships between table pairs
Please allow us to create relationship between two tables using a combination of 2 columns instead of single column, if none of the single columns have unique values. Sometimes it is not possible to create summary tables with unique values for any column.
RE: Add the ability for multiple relationships between table pairs
Allow data modellers to define more complex relationships between tables. Currently, no compound key relationships are allowed, which hinders modelling and design, as now what should be a simple process becomes more complicated with users needing to manufacture unique non intelligent keys.
RE: Add the ability for multiple relationships between table pairs
The lack of this functionality is very disappointing. The workaround does not apply on all types of columns and disables DirectQuery mode.
RE: Add the ability for multiple relationships between table pairs
I support this - the lack of composite key support in the query editor infers that Microsoft demands a surrogate key, which is senseless. Modelling should be allowed to create the (minimum) required key to support the business intention, and the tool reading from the datastore needs to support this.
I've just hit this in an otherwise poorly modelled legacy store which I wanted to use PowerBI to give the business faster, more collaborative access to the data.
I will work around this gap by creating the join in a view. That is going to mean I need a deployment in order to create that view.
RE: Add the ability for multiple relationships between table pairs
Absolutely agree.
RE: Add the ability for multiple relationships between table pairs
The concatenation solution does not work in direct query mode. When there are large tables, it is not practical to use import mode. Please fix this.
RE: Add the ability for multiple relationships between table pairs
I've encountered numerous situations where this would have been needed as there are e.g. multiple roles between a transaction table and an entity table. This becomes specially crucial if the entity instances (e.g. Departments) are part of a hierarchy you need to be able to filter by using a higher level common nominator (e.g. Business Unit) and any of the related departments separately -- this an actual business case unsolvable by hierarchy model supported by Power BI :(
I've actually have had PowerBI disqualified by the end users due to this limitation in few occasions, simply making copies of the large and complex hierarchies to server few different roles isn't simply going to cut it, end users want to pick the hierarchy level from one simple selection.
RE: Add the ability for multiple relationships between table pairs
This is a major point of frustration for me. Too often I would need to join on 3 or four columns, adding new columns as a workaround is just frustrating and unnecessary. Also, I don't want them in the same table, so merging the tables in the query editor is useless.
RE: Add the ability for multiple relationships between table pairs
I don't understand why this isn't already supported. Even Access allows joining on multiple primary keys.