Microsoft Office Tutorials and References
In Depth Information
FIguRE 7-46 A warning about a missing relationship caused by the Period table.
This warning is provoked by the Period table, which does not have any relationships with
other tables in the model. You can disable this warning by pressing the Detection button
on the PowerPivot tab of the ribbon, which is the one highlighted in Figure 7-47.
FIguRE 7-47 The Detection button on the PowerPivot tab of the ribbon disables the detection
of missing relationships.
At this point, you can define a single measure that checks the selected value of the Period
table and uses a DAX expression to return the corresponding calculation. Because there
are no relationships with the Period table, the selected value in the Period table is always
the one chosen by the user whenever that table is used as a filter, or the selected value is
the corresponding value in a row or a column whenever Period is used in Row or Column
labels. In general, we follow this generic pattern:
= IF( COUNTROWS( VALUES( Period[Period] ) ) = 1,
IF( VALUES( Period[Period] ) = "Current", < expression> ,
IF( VALUES( Period[Period] ) = "MTD", < expression> ,
The first condition checks that there are not multiple values active in the filter context. In
such a case, you should avoid any calculation because of the ambiguity of having multiple
active values; otherwise, you should generate an error in the calculation, instead of returning
a wrong value without warning the user. Then in the next step, each value is checked by a
different IF statement, which evaluates the correct expression corresponding to the Period
value. Assuming you have all the measures previously defined in this chapter, you need to
replace the expression tag with the corresponding specific measure. For example, you can
define a generic CalcLineTotal measure, which is used to apply one or more of the operations
described in the Period table to the LineTotal measure:
Search JabSto ::

Custom Search