Forum Discussion

boujarwah's avatar
boujarwah
Regular Visitor
4 years ago

How to Use Measures in Calculated Columns?

Hi all,

 

I have a report I'm building with a long list of employees, their start date, and end date. I have a slicer that is linked to a date table. I have created two measures based on the slicer: 

/* First date in slicer:

Selected Value MIN = CALCULATE(MIN('DATE Dim'[DATE]),ALLSELECTED('DATE Dim'))

/* Second date in slicer:

Selected Value MAX = CALCULATE(MAX('DATE Dim'[DATE]),ALLSELECTED('DATE Dim'))

 

I am now looking to create a calculated column (I believe this is the only way, as it needs to be calculated per each row) that calculates the days worked within the slicer dates. 

 

Days Worked Final = IF(Employees[Sign Off Date].[Date]=BLANK(),0,IF(Employees[Sign Off Date]<Employees[Selected Value MIN],0,IF(Employees[Sign On Date]>Employees[Selected Value MAX],0,MIN(Employees[Selected Value MAX],Employees[Sign Off Date])-MAX(Employees[Selected Value MIN],Employees[Sign On Date]))))
 
I have tried a number of different if statements to try and create the column, however, the results always seem to ignore the measures, and simply use the start and end date to calculate the days. 
 
I have added the measures as cards to the report page to ensure that the value returned is the one I am setting in the slicer, and it appears correctly there, so i am wondering why is it not applying to the calculated column correctly.
Need Help

2 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    Calculated columns are only calculated during data refresh so they don't take into account any slicers or filters. If you need something which reacts with slicers and filters you will need to create a measure, not a column.

    You can turn your code for a calculated column into a measure by wrapping every column reference in SELECTEDVALUE().

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, boujarwah 

     

    Are you able to provide some of the example data? What kind of results do you expect, and can you show them in pictures? Although you can't create calculated columns based on measure, you may be able to achieve your needs in other ways.

     

    Best Regards