Dynamic calculated column does not refresh

[toha]

Hi guys,

i wanted to create a calculated column in my date table based on two measures in another table (for filtering in different barcharts)

column for my 4-weeks-period looks somewhat like this:

if ( date_table >= config_table[measure_4weekPeriodStart] && date_table <= config_table[measure_4weekPeriodEnd] ; TRUE(); FALSE() )

measure_4weekPeriodStart and measure_4weekPeriodEnd calculate from fields of a config table and can change due to different roles <-- these works fine

the calculated column does not seem to refresh - where is my mistake? Can someone help?

best regards

Tobias

[martina]

Hi,

calculated column gets refreshed only when you run data load. It doesn't change dynamically based on the measure / filter change.

[richbenmintz]

As alluded to Column Calcs are only recalculated on data refresh, are you able to provide a sample .pbix? would help me understand what the requirement is and what type of measure would provide you with what you need. The new relative date filter functionality may also provide you with the behaviour you desire.

[toha]

hey,

thanks for your replies.

Maybe i can descripe my problem different, because i think that i might be a problem with the row level security and my data model and my thinking.

I have simplified my 6 tables

1. User
   1. User ID <-- this is where i filter the permissions for my roles
2. Config
   1. User ID
   2. Report Start Date <-- from this cell i calculated different dates as measures (first of month, last of month, start of 4 weeks period, end of 4 weeks period and so on) <-- this report state date can be different for different users
3. Date
   1. Date ID
   2. Some Date Columns
4. Report Data 1
   1. User ID
   2. Date ID
   3. Formatted Timestamp column
   4. Value X
5. Report Data 2
   1. User ID
   2. Date ID
   3. Formatted Timestamp column
   4. Value Y
6. Report Data 3
   
   1. User ID
   2. Date ID
   3. Formatted Timestamp column
   4. Value Z

What I want to do is - i want to show my report data in two periods: "last month" and "last 4 weeks". Therefore my thoughts were to add 2 calculated column to the Dates table with "is in 4 weeks period?" and "is in last month period?" so i can add this columns as filters to different visualisations.

What I did in the meantime: i added a similar calculated column to one of the report data tables and there it works... but if i do this as a workaround i have to add the calculated column to all of my report data tables.

[Eric_Zhang]

---

@toha wrote:

hey,

thanks for your replies.

Maybe i can descripe my problem different, because i think that i might be a problem with the row level security and my data model and my thinking.

I have simplified my 6 tables

1. User
   1. User ID <-- this is where i filter the permissions for my roles
2. Config
   1. User ID
   2. Report Start Date <-- from this cell i calculated different dates as measures (first of month, last of month, start of 4 weeks period, end of 4 weeks period and so on) <-- this report state date can be different for different users
3. Date
   1. Date ID
   2. Some Date Columns
4. Report Data 1
   1. User ID
   2. Date ID
   3. Formatted Timestamp column
   4. Value X
5. Report Data 2
   1. User ID
   2. Date ID
   3. Formatted Timestamp column
   4. Value Y
6. Report Data 3
   
   1. User ID
   2. Date ID
   3. Formatted Timestamp column
   4. Value Z

What I want to do is - i want to show my report data in two periods: "last month" and "last 4 weeks". Therefore my thoughts were to add 2 calculated column to the Dates table with "is in 4 weeks period?" and "is in last month period?" so i can add this columns as filters to different visualisations.

What I did in the meantime: i added a similar calculated column to one of the report data tables and there it works... but if i do this as a workaround i have to add the calculated column to all of my report data tables. 

---

Instead of calculated column, maybe you can apply the DAX formula as filter.

4weeksPeriodValue =
CALCULATE (
    SUM ( 'requiredTable'[requiredValue] ),
    FILTER (
        filteredTable,
        MAX ( 'date_table'[date] ) >= config_table[measure_4weekPeriodStart]
            && MAX ( 'date_table'[date] ) <= config_table[measure_4weekPeriodEnd]
    )
)

[chrissembroski]

Eric,

I am using that expression format for a measure. However, I'd like to be able to use that measure within a calculated column. But it sounds like changing the slicer on the measure calculation will not dynamically affect the calculated column's values.

What could be another way of getting this to work? 

I am trying to select a baseline 12-month period to calculate a measure. In this case it is for base line energy consumption. I want to be able to run some calculations on other days/months/years to normalize the data for comparison purposes.

Thanks,

Chris Sembroski