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
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

       

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

        Hi,

         

        Please create a table like this by Enter Data:

        Then create a check measure:

         

        check = IF([TYpe of Monitoring]=SELECTEDVALUE(TypeSlicer[Type]),1,0)

         

        Apply this measure to the original visual by setting check=1.

        Choose the above [Type] as a slicer, when select one value, the result shows:

         

        Best Regards,

        Giotto Zhi