Forum Discussion
Helper Idata model design question - merge vs. relationship
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.
Super User
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.
Helper IThanks 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
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.