Translating Power BI relations to SQL

[Roinka]

Hi,

Would highly appriciate it if some one can explain to me in terms of SQL so i can better udnerstand it the meaning of PBI relations.

We have a sales table with orders containing a country key and a Country dimension table also containing a country key.

Lets say we have a relation pointing from the country table to the sales table and then I create a matrix showing the total sales per country. To my understanding this will be the same as:

select C.CountryName, S.totalSales from Countries C, Sales S

where C.countryKey=S.countryKey;

1. Is it possible to technically create a relation between Sales and Country which is in the opposite direction? if so, how is it done and what is the meaning of it?

2. What is the menainig of "Both" under "Cross filter direction"?

Thank you in advance.

[SteveCampbell · Accepted solution]

Its good to think of this in terms of FILTERS, not a SQL join. Power BI does everything on filters.

Lets say you have two tables, as below:

The relationship is made on the ID column.

Lets say now I make this table with Continent from Countries and Sum of Sales from Sales:

This has a unique query for each row, that has a different filter.

Lets look at the first row, America.

It will first apply the filter in the Countries table to the continent America. It will then return a list of IDs (as that is what the relationship is on) - in this case, {1,2} which is USA and Mexico.

It passes this down the relationship to the Sales table, and filters the ID column of Sales to IDs {1,2}.

So in SQL, the Sales column of the first row of America would be like this:

Select Sum ( S.Sales )

FROM SALES S

INNER JOIN 
( Select ID
FROM Countries 

WHERE Continent like 'America' ) C

on C.ID = S.ID

The arrow direction is important. This shows what direction the filter can travel. We said that the Countries table passes the list of IDs as a filter down the relationship to the Sales table. This is possible, as the arrow points from Countries to Sales. However, it cannot go the other way. Look at this table, of all IDs from the Sales table and the count of IDs in the Countries table:

This says that for each ID in the Sales table, there are 5 in the Countries table. Obviously, this is not right, as there is only 1 corresponding row in the Countries table. This is because, the filter cannot go from Sales to Country.

Again, let's look at the first row, ID 1:

The Sales table is filtered to ID {1}.

The filter cannot be passed anywhere, as the arrow direction does not allow it, so the Countries remains unfiltered.

The ID column of Countries table is then counted, which is 5.

So then, why do we have this arrow? It would be my reccomendation to never use bidirectional filtering if possible, or unless you're conifdent on what you're doing and have a very good reason.

Heres an issue that could happen. Lets say we have another table, SalesPerson:

If we add a table of  Countries[Country] and First( SalesPerson[Name]):

We notice that Mario is missing in Spain. This is because there were no sales for this country, and the filters are allowed to pass through.

What happens for the row Spain:

Countries is filtered to Spain

List of IDs are generated  = {5}.

The list of IDs is passed to the Sales table. The Sales table is filtered to {5}.

Now, this list of IDs is generated again. However, there was no ID 5, so there are no IDs generated, and the list is empty.

The filter is passed to SalesPerson, which is empty, so this table is filtered to show nothing.

We can say there are the following steps when filtering:

1. Apply the filter in the table it is applied

2. Collect a list of values for any columns with relationships

3. Pass these filters down, as long as the arrow direction allows it

4. Apply new filters to the new tables

5. Repeat until all filters and relationships have been applied

6. Calculate appropriate expressions (SUM, Average) over new filtered tables

Always try and use STAR schema. Two way relationships should be avoided when possible.

As much as possible, make sure all filters are from dimension tables, while any expression (such as a SUM or AVERAGE of something) is from the fact tables. This also helps to not have missing fields, or handle blanks correctly. This holds true on graph axis too. For example, a bar graph axis may have “Country”, while the values might be “Sum of Sales”.

Each bar you see, Power BI is applying a filter to that specific product, then calculating the sum of sales, after this filter has been applied and passed down. This means the axis on graphs are actually filters. Therefore, your axis should be from Dimension tables, while the values from the fact.

Same with CALCULATE statement- try to have the expression from the fact, and filters in the dimensions. Even if you have a small dataset, Star schema can help with graph creation and drastically improve the ease of writing DAX.

Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!

Stay up to date on  
Read my blogs on