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 

 

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

 

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.