Measure inside Calculated Column; but measure depends on slicer selection

Question

I have a tricky situation in Microsoft Power BI, and DAX language:

I am developing a new Calculated Column called Status_CC in a table called Customers; we refer to this formally as - Customers[Status_CC].

This calculated column (Customers[Status_CC]) has a number of conditions in its derivation, I am using SWITCH statement to develop it.

i.e.

Status_CC = SWITCH(

                     TRUE(),

                     .........

                   )

One of the conditions to develop the this Customers[Status_CC] calculated column is: Customers[HireDate] > [BonusDate].

The intersting part is, HireDate is an existing column in the Customers table.

However, [BonusDate] is a measure; this measure is developed using another table called WorkHistory.

A column (called PayCategory) from the WorkHistory table acts as a slicer in the report visual. The PayCategory column determines the value of the [BonusDate] measure.

I am using the DAX function ALLSELECTED on the slicer - the WorkHistory table's PayCategory column, to develop the [BonusDate] measure.

My question is, will the calculated column Customers[Status_CC] work correctly, if it depends on the [BonusDate] measure, which in turn depends on another table WorkHistory, which feeds PayCategory that acts as a slicer ?

I don't see any syntax error in Customers[Status_CC], but not sure whether the numbers are right.

My final report visual in Power BI Report View has:

-several columns from the Customers table, including the calculated column Customers[Status_CC]

-a slicer with PayCategory from the WorkHistory table that dictates the value of the [BonusDate] measure.

Any advice, please?

Answer 1

Measures used in calculated columns are calculated at model refresh time for each row. The row context is transformed to a filter context during the calculation of the measure, and is the only active filter for the measure calculation. So no report filters or slicers would be active at that point.

Note for that non-measure expressions the row context is not transformed to a filter context, so you would see a global total on each row, unless you explicitly use calculate which always changes the row context into a filter context.