compute the amount invoiced for the order selection is a slight variation from the previous one, as you can see in the following code:
Click here to view code image
Amount Invoiced := CALCULATE (
SUM ( OrdersInvoices[Amount] ),
CROSSFILTER ( OrdersInvoices[Invoice], Invoices[
)
You sum the Amount column in the bridge table and the CROSSFILTER function activates bidirectional filtering between the bridge table and Invoices. The result of using this formula is much more interesting because you can now easily spot the amount ordered and invoiced for every order, and you can obtain reports like the one shown in Figure 3-21.
FIGURE 3-21 Using the bridge table, you can produce a report showing the amount ordered and invoiced.
Conclusions
In this chapter, you learned how to handle different scenarios with multiple fact tables that are, in turn, related through dimensions or through bridge tables. The most important topics you learned in this chapter are as follows:
If you denormalize too much, you reach a point at which your tables are over-denormalized. In such a scenario, filtering different fact tables becomes impossible. To correct this, you must build a proper set of dimensions to be able to slice values from the different fact tables.
Although you can leverage DAX to handle over-denormalized scenarios, the DAX code quickly becomes too complex to handle. A change in the data model makes the code much easier.
Complex relationships between dimensions and fact tables can create ambiguous models, which cannot be handled by the DAX engine. Ambiguous models must be solved at the data-model level by duplicating some tables and/or denormalizing their columns.
Complex models like orders and invoices involve multiple fact tables. To model them the right way, you must build a bridge table so that the information is related to the correct entity.