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,

 

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

  • gathenjic's avatar
    gathenjic
    Frequent Visitor
    6 years ago

    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's avatar
    MFelix
    Super User
    6 years ago

    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's avatar
    gathenjic
    Frequent Visitor
    6 years ago

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

     

    Am open to a new approach too

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    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's avatar
    gathenjic
    Frequent Visitor
    6 years ago

    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

     

    DistrictResponse_TypeActivity_Start_DateActivity_End_DatePost_start_DatePost_End_Date
    District1Round 107/02/202011/02/202012/02/202015/02/2020
    District2Round 107/02/202011/02/202012/02/202015/02/2020
    District1Round 214/03/202018/03/202020/03/202025/03/2020
    District2Round 214/03/202018/03/202020/03/202025/03/2020

     

    Provincedistrictdate_monitored
    Province1District107/02/2020
    Province1District108/02/2020
    Province1District209/02/2020
    Province1District210/02/2020
    Province1District111/02/2020
    Province1District114/04/2020
    Province1District115/03/2020
    Province1District115/03/2020
    Province1District216/03/2020
    Province1District214/03/2020
  • MFelix's avatar
    MFelix
    Super User
    6 years ago

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

     

    Thank you