This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hi
The Tabular Object Model has multiple fact tables and multiple dimensions. Some fact tables does not have a relationship a dimension.
When using our model in Excel in a pivot table, it gives for all the combination between the fact and dimension a Cartesian product. It shouldn't give any results at all, just a blank value or no record at all. In the Multidimensional model this works correct.
.
Left side: Multi dimensional model, right side Tabular Object Model. All the measures are located in another fact table.
A possible fix is to make a relationship between all facts and dimensions, but we want to avoid this option.
Edit:
Found an old thread with some info, but no solution. Is it even possible to resolve this issue without having a relationship?
relationship-without-a-measure-doesnt-work-when-analyzing-in-excel-ssas-tabular
Hi @Jordi Perreman , thanks for your posting. Normally, when you get wrong results in Excel, it means the model does not have correct relationships. Please refer to this link:
A schema can have one or more facts, but these facts are not linked by any key relationship. It is best practice not to join fact tables in a single query as you would whey querying a normalized/transactional database. Due to the nature of many to many joins, etc - the results would be incorrect if attempted.
The answer you are looking for is that you need to "drill across" which basically means that you are querying each fact table (schema) separately and merging the results. This can occur using SQl or preferably via a reporting/analytics tool that you may have which referenced the data warehouse.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(32, 95%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAS%3C/text%3E%3C/svg%3E)
The behavior of the MD model in your example is the result of a non-default setting for the IgnoreUnrelatedDimensions property. You can't have this in Tabular, seeing as there're no 'dimensions' in TOM.
While this can be resolved, it could take quite a bit of coding. See e.g. this thread
Using the ISCROSSFILTERED dax function in combination with the if and normal dax expression did the trick. Thank you for providing this solution!