data model design question - merge vs. relationship

[jkapso751]

Hi all,

Novice here - just trying to figure out the best approach for designing the model.

My example has two tables.  I have three columns (company code, customer type, and product) which, when concatenated, can be used to map to a "business function."

I want to calculate sales totals by business function and use them in allocation ratios, where the total sales by business function is the denominator and the sales by product is the numerator.

Which is the better approach here: 1) use a merge in power query to bring business function into table 1, or 2) use a relationship between tables 1 and 2 based on the concat column?  Approach #1 would be a lot easier, but I've heard about the dangers of "auto-exist."  Approach #2 aligns w/star schema approach, but I can't use the ALL function to, for example, remove filters on both business function and product (ALL requires the columns to be in the same table).

Table 1 has about 80K rows for one month of data and I'd like to continue to add data each month (so one year of data will have ~1M rows).

Many thanks in advance for your support!

John

[jkapso751]

Thanks again DataInsights, appreciate your input.

What if I want to remove filters on columns in different tables?  I tried that and got an error message saying the function only works with columns in the same table.  Do I need more sophisticated DAX code for that (e.g., for creating virtual tables, etc.).

[DataInsights]

@jkapso751,

Do you have a sample pbix you could share via one of the file services like OneDrive? Also include the requirements and expected result.

[jkapso751]

Thanks again DataInsights.

I think figured it out.  Apologies - not sure if I'm allowed to use OneDrive to share files with people outside my company.  Hopefully the snippets below will help?

Data and Mapping Tables - relationship between Concat and Custom.

In the matrix visuals shown below, all the table columns are from the Data table except for Business Function.  Net Revenue and Revenue allocation denominator are measures.

Net Revenue = CALCULATE(sum(Data[Amount]),Data[Account]="Net Revenue")

If I want my revenue allocation denominator to be the total by Entity (as an example), using the ALL statement seems to work if I only use Business Function as the column argument.

However, I also need to remove the filter on Product (grouped under Business Function), or else I get the following result:

The measure is computing the totals by product as the filter is only removed for Business Function.  For example, the -8.5M for CanadaProd is there because it's including -8.5M from Manufacturing.

If I try to include Product as an additional column argument, I get the following error:

Got it to work with a simple fix to the syntax: just needed to wrap the Business Function column argument with a separate ALL statement.

Hopefully I'm on the right track now?

Many thanks!!

John K.

[DataInsights · Accepted solution]

@jkapso751,

That looks right, creating individual filter arguments in CALCULATE. If you want to complete the star schema, you could create tables DimEntity, DimProduct, etc. This would enable you to add attributes to each dimension (e.g., Product Group) and slice by these in visuals. If you have an overall filter or slicer for Product or Business Function, consider using ALLSELECTED instead of ALL so the filter/slicer remains in effect. Glad you got it working.

[DataInsights]

@jkapso751,

A star schema is generally the best approach. However, when tables have a one-to-one relationship, I prefer to merge them since one table is essentially an extension of the other. This also prevents bidirectional relationships in your model (best practice). You could look at creating dimension tables such as DimBusinessFunction, etc. and create a star schema.

[jkapso751]

Thanks DataInsights.

If I were to create a dimension table, the relationship between business function and concat would be many-to-one (many:concat to one:function).  How would I then use the table columns in a measure with an ALL or REMOVEFILTERS statement if I needed to remove filters from columns in different tables (the function table and my fact table)? 

[DataInsights]

@jkapso751,

You could write a measure like this:

Business Function Ratio to Total =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], ALL ( DimBusinessFunction[Business Function] ) )
)

Total Sales = SUM ( Table1[Sales Amount] )

It's generally best to use dimension tables in your visuals and apply/remove filters on dimension tables (via the UI or DAX), which will result in the fact table being filtered.