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.
Helper IThanks 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
Super User
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
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.