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

[gathenjic]

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

[MFelix]

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]

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

Province1	District2	07/02/2020
Province1	District2	08/02/2020
Province1	District2	09/02/2020
Province1	District2	10/02/2020

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

Region	District	Response_Type	Activity_Start_Date	Activity_End_Date	Post_start_Date	Post_End_Date
Province1	District1	Round 1	07/02/2020	11/02/2020	12/02/2020	15/02/2020
Province1	District2	Round 1	07/02/2020	11/02/2020	12/02/2020	15/02/2020
Province1	District3	Round 1	07/02/2020	11/02/2020	12/02/2020	15/02/2020
Province1	District4	Round 1	07/02/2020	11/02/2020	12/02/2020	15/02/2020
Province5	District5	Round 1	07/02/2020	11/02/2020	12/02/2020	15/02/2020
Province6	District6	Round 1	07/02/2020	11/02/2020	12/02/2020	15/02/2020
Province6	District7	Round 1	07/02/2020	11/02/2020	12/02/2020	15/02/2020
Province1	District1	Round 2	14/03/2020	18/03/2020	20/03/2020	25/03/2020
Province1	District2	Round 2	14/03/2020	18/03/2020	20/03/2020	25/03/2020
Province1	District3	Round 2	14/03/2020	18/03/2020	20/03/2020	25/03/2020

Table 3: List of districts for table relationships purpose

PROVINCE	DISTRICT
Province1	District1
Province1	District2
Province1	District3
Province1	District4
Province5	District5
Province6	District6
Province6	District7

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]

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]

@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/Ed9sZ88gRqZInbZAVcSKowABCY...

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

[v-gizhi-msft]

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]

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

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.

[v-gizhi-msft]

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]

@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

[gathenjic]

@MFelix @v-gizhi-msft  

The measure takes in the start date and end date irresppective of the location (district)See the marked are the 2 dates that the measure is using to assign IN or END process. My extection is it will evaluate based on the district too

[MFelix]

Hi @gathenjic ,

You want the all the lines presented show the same value on the measure or for each one should be a diffenrent value?

[gathenjic]

@MFelix , the Value should differ based on dates supplied on related row in both tables. I think its more about defining how the relationships are and at the same time there is no unique key in either but are related to a master district tabled

District	Response_Type	Activity_Start_Date	Activity_End_Date	Post_start_Date	Post_End_Date
District1	Round 1	07/02/2020	11/02/2020	12/02/2020	15/02/2020
District2	Round 1	07/02/2020	11/02/2020	12/02/2020	15/02/2020
District1	Round 2	14/03/2020	18/03/2020	20/03/2020	25/03/2020
District2	Round 2	14/03/2020	18/03/2020	20/03/2020	25/03/2020

Province	district	date_monitored
Province1	District1	07/02/2020
Province1	District1	08/02/2020
Province1	District2	09/02/2020
Province1	District2	10/02/2020
Province1	District1	11/02/2020
Province1	District1	14/04/2020
Province1	District1	15/03/2020
Province1	District1	15/03/2020
Province1	District2	16/03/2020
Province1	District2	14/03/2020

[MFelix]

Can you please add the result you want on the last table based on the date selection?

Thank you

[MFelix]

Hi @gathenjic ,

Measure are calculated based on context so the more values you add to your filters, slicers, visuals an so on the more the result of the measure changes so in this cases if you have the district and province the result will be done with the lowest level of granularity.

[gathenjic]

@MFelix Is there a way around it to enable get the desired output? 

Am open to a new approach too

[gathenjic]

https://worldhealthorg-my.sharepoint.com/:u:/g/personal/gathenjic_who_int/Ed9sZ88gRqZInbZAVcSKowABCY...