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

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

      • gathenjic's avatar
        gathenjic
        Frequent Visitor
        6 years ago

        v-gizhi-msft MFelix 

         

        Amazing both dax worked. Great!!!!

         

        Am discovering new things as i go.  Here is where I am stuck at, The activity can be repeated in the same district at different dates. How do i make the formular to look into specific dates for that specfiic round

    • MFelix's avatar
      MFelix
      Super User
      6 years ago

      Hi gathenjic ,

       

      Although the answer from v-gizhi-msft  is absolutly spot on I would use the SWITCH function instead of the IF inside if

       

      TYpe of Monitoring =
SWITCH (
    TRUE ();
    MIN ( HH_Monitoring[date_monitored] )
        >= MIN ( 'Activity Calendar'[SIAs_Start_Date] )
        && MIN ( HH_Monitoring[date_monitored] )
            <= MIN ( 'Activity Calendar'[SIAs_End_Date] ); "IN_Process";
    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"
)

       

      To what concerns the last part just create a new table with the 3 states (unrelated table) then create the following measure:

      Status Selection = IF(CONTAINS('Status';'Status'[Status];[TYpe of Monitoring]) ;1;0)

      Use this measure to filter the table visualization setting the value to equal 1.

       

      check PBIX file attach.