Normalized Model Measure by Dimension

[Anonymous]

Dear community,

I started working with PBI and my question is the following:

I created a table with a measure and a dimension. The data model (created in visual studio)  is normalized and the tables are not directly related, in between i have two additional table with many to one cardinalities in the direction of my fact table. My questions are:

1. How shall i understand the data model in PBI? Are all the tables in the data model outer joined? Problably no, since i cannot distribute my measure by dimension values.

2. How can i deal with a normalized model where i need to count or sum fields distributed by dimension values in another table without a direct relationship?

(do you know any link to read about this?)

Thanks for the help

felipe

[v-xicai · Accepted solution]

Hi  @Anonymous ,

For your question 1, you use a Data Analysis Expressions (DAX) formula to define the table’s values, but the DAX formula is not in a measure concept. Unlike tables created as part of a query, calculated tables created in Report View or Data View are based on data you’ve already loaded into the model. For example, you might choose to union or cross join two tables. It just like an intermediate table which is stored as part of the model rather than calculated on the fly or as part of a query.

Just like normal tables, calculated tables can have relationships with other tables. The columns in your calculated table have data types, formatting, and can belong to a data category. You can name your columns whatever you want, and add them to a report visualization just like other fields. Calculated tables are re-calculated if any of the tables it pulls data from are refreshed or updated in any way.

For your question 2, you can create relationship between the calculated table and your fact table. If there is no relationship between these table, you can add the calculated table' dimension fields to the FILTER function when you need to calculate SUM or others.

You can refer to the links:https://docs.microsoft.com/en-us/power-bi/desktop-calculated-tables, https://blog.crossjoin.co.uk/2015/09/23/calculated-tables-in-power-bi/.

(Please understand that this link is provided “AS IS” with no warranties or guarantees of content changes, and confers no rights.)

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks Amy for your response! It is now clearer to me. I was wondering if there is an automatic notification to my email account.... I did not see your answer.

Best reagards,

Felipe