Microsoft Power BI, DAX : Measure inside Calculated Column; but measure depends on slicer selection

[snph1777]

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?

[HotChilli]

It's not good practice to use a measure in a calculated column.  

It definitely won't respond to a slicer since calculated columns are evaluated at refresh or data load.

[snph1777]

But I am seeing values in the CalculatedColumn; you cannot say that the measure is not responding inside the CalculatedColumn definition

[Anonymous · Accepted solution]

Hi @snph1777 ,

The problem is that calculated columns are suitable for handling static data, for example, defining expressions that are strictly bound to the current row.

In the above case, part of the data in the calculated column comes from the slicer. When we press the Enter key, the calculated column performs a calculation. But when we change the slicer, the calculated column will not calculate again. The reason is that it is calculated when the in-memory database is refreshed and then stored in the model.
Hope these help.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data