Forum Discussion

gathenjic's avatar
gathenjic
Frequent Visitor
6 years ago

How to Assign text value based on dates while the date variable are in 2 different tables

I have 2 tables. Table1 for campaign monitoring which have date_monitored

Table 2: is for campaign calendar - it has Campaign_start_date, Campaign_end_date, Monitoring_start_date and Monitoring_end_date.

 

Monitoring done during campaign is considered as inprocess and after campaign end date its called EndProcess

 

My measure below is meant to Assign the type of monitoring based on dates when monitoring was done. Am however am getting error. I need guidance

 

Type IM = if(MIN('4344_Household Monitoring'[date_monitored])>=MIN('SIAS calendar'[SIAs Start Date]) && MIN('4344_Household Monitoring'[date_monitored])<=MIN('SIAS calendar'[SIAs End Date]),"Inprocess", if(MIN('4344_Household Monitoring'[date_monitored])>=MIN('SIAS calendar'[IM start Date]) && MIN('4344_Household Monitoring'[date_monitored])>=MIN('SIAS calendar'[IM End Date]),"End_process","ERROR"))

Data Protection

16 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi gathenjic ,

     

    Best option is to create a calendar table to connect both tables and then use that one to make the filtering you need.

     

    If you can share a sample data and expected result would be more helpfull to guide you in the right direction.

    • gathenjic's avatar
      gathenjic
      Frequent Visitor
      6 years ago

      MFelix Thanks for getting intouch to SOLVE

       

      How to I attached the .pbix file?

       

       Below is how my table structure looks

       

      Table 1: HH-Monitoring - This is the data collection form. Activities are monitored as they happen (Inprocess) and after completion (End_process)

       

      Province1District207/02/2020
      Province1District208/02/2020
      Province1District209/02/2020
      Province1District210/02/2020

       

      Activity Calendar: Showing dates when Activity started and Ended (inprocess) and dates of post campaign activities

      RegionDistrictResponse_TypeActivity_Start_DateActivity_End_DatePost_start_DatePost_End_Date
      Province1District1Round 107/02/202011/02/202012/02/202015/02/2020
      Province1District2Round 107/02/202011/02/202012/02/202015/02/2020
      Province1District3Round 107/02/202011/02/202012/02/202015/02/2020
      Province1District4Round 107/02/202011/02/202012/02/202015/02/2020
      Province5District5Round 107/02/202011/02/202012/02/202015/02/2020
      Province6District6Round 107/02/202011/02/202012/02/202015/02/2020
      Province6District7Round 107/02/202011/02/202012/02/202015/02/2020
      Province1District1Round 214/03/202018/03/202020/03/202025/03/2020
      Province1District2Round 214/03/202018/03/202020/03/202025/03/2020
      Province1District3Round 214/03/202018/03/202020/03/2020

      25/03/2020

       

      Table 3: List of districts for table relationships purpose

      PROVINCEDISTRICT
      Province1District1
      Province1District2
      Province1District3
      Province1District4
      Province5District5
      Province6District6
      Province6District7


      Expectation: To use look at the date of Monitoring against the SIAs calendar and classfy as inprocess, end process otherwise if not within those dates its test data for training

      • v-gizhi-msft's avatar
        v-gizhi-msft
        Community Support
        6 years ago

        Hi,

         

        Could you please tell me that why the column in your formula such as [SIAs Start Date] or [SIAs End Date]does not exist in your sample table?

        Please share the expected result as a screenshot for me to help you further.

        You can share your pbix file by OneDrive for Business if convenient.

        And remember to remove any sensitive data.

        Thanks.

         

        Best Regards,

        Giotto Zhi