Forum Discussion

toha's avatar
toha
New Member
9 years ago

Dynamic calculated column does not refresh

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 th...
Need Help
martina's avatar
martina
Advocate I
9 years ago

Hi,

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

  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar
    9 years ago

    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's avatar
      toha
      New Member
      9 years ago

      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's avatar
        Eric_Zhang
        Microsoft Employee
        9 years ago
        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]
    )
)