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_...
Data Protection
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

  • gathenjic's avatar
    gathenjic
    Frequent Visitor
    6 years ago

    v-gizhi-msft MFelix 

     

    In preparing a sample pbix, I renamed the SIAs start and end date to activity start and End date. In the pbix file shared i have a measure that i was string to create for calculation. maybe it will give more clarity

     

    Below is the expected output 

     

    https://worldhealthorg-my.sharepoint.com/:u:/g/personal/gathenjic_who_int/Ed9sZ88gRqZInbZAVcSKowABCYOtCr_fEEtkSkMXN70gEA?e=IWwYwd

     

    Expected Output: bases on Monitotin_date and Activity dates and IM dates

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

      Hi,

       

      It is an incorrect use of IF function that caused error, please try this:

       

      TYpe of Monitoring = 
IF (
    MIN ( HH_Monitoring[date_monitored] )
        >= MIN ( 'Activity Calendar'[SIAs_Start_Date] )
        && MIN ( HH_Monitoring[date_monitored] )
            <= MIN ( 'Activity Calendar'[SIAs_End_Date] ),
    "IN_Process",
    IF (
        MIN ( HH_Monitoring[date_monitored] )
            >= MIN ( 'Activity Calendar'[IM_start_Date] )
            && MIN ( HH_Monitoring[date_monitored] )
                <= MIN ( 'Activity Calendar'[IM_End_Date] ),
        "END_Process",
        "TestData"
    )
)

       

      The result shows:

       

      Best Regards,

      Giotto Zhi

      • gathenjic's avatar
        gathenjic
        Frequent Visitor
        6 years ago

        v-gizhi-msft 

         

        Thanks That worked, However at the back of my mind i was hoping to be able to use the TYpe of Monitoring in a slicer. This is not working. I Thinks that approach will not work for that.

         

        Do you have an idea how i can calculate that measure and be able to use it in a slicer