what is the best many-to-many tutorial out there for Power BI?

[pmdci]

I have an Excel 2016 file in OneDrive (personal) containing a bunch of tables, from which I want to create a Power BI report.

Suppose that I have two simple tables: country and languages.

I want to specify the official languages spoken in a country. As you know, one country can speak multiple languages so:

• France - French
• Germany - German
• Italy - Italian
• Switzerland - German, French, Italian, Rumantsch

So clearly this calls for a many-to-many relationship.

Prior to Power BI and SQL 2016, when trying to create N:N (many-to-many) relationships in a tabular model, we would often follow the Italian Way (thanks to Marco Russo and Alberto Ferrari, see here). This involves having a bridge table between country and language, and a substantial amount of DAX code.

However there has been recent improvements in the tabular model (Power BI, SSAS 2016) which now allows for us to specify cardinalities and direction of relationships, as well as relationship type. Thaking those improvements into account, what would be the best practices for handling N:N in this case?

Regards,

P.

[ankitpatira]

@pmdci If you only have two tables like you've mentioned in your example then it should not be an issue when you import into power bi desktop, it will try and create relationships automatically for you and determine cardinality. From what I've seen is when you have many tables to import, it will create relationships between them but not make all active. If I manually change cross filter direction from Both to Single, then I am able to make those relationships active in power bi.

When I have many tables to import from sql server to power bi what I normally do is choose DirectQuery machnaism initially. Once all table loaded I change from DirectQuery to Import and this keeps all PK FK relations in power bi same as sql server without needing to do any manual work to manage relations.

[pmdci]

Thanks for the reply but I was looking for some specifics about the issues I have raised.

Turns out that I will always need a relationship table, and that there is a difference when importing the data into Power BI vs reading it from an Excel model. If importing the data, I will be able to specify cardinality, direction and all that jazz which will be enough for supporting my N:N requirement.

On the other hand if I have the data being read from Excel (say, an Excel file that is in my OneDrive), I WILL have to follow the Italian Method to make the N:N relationship work. Just having a relationship table is not enough.

I had to find this the hard way by performing tests. But I got there in the end 🙂

Regards,

P.