Forum Discussion

elinevans's avatar
elinevans
Helper I
5 years ago
Solved

Active Client ID

  Here is an example of the type the data I'm using. Each client can have more than 1 service and I want to find clients active in a service within a time period (say between date x and date y...
Need Help
  • Samarth_18's avatar
    Samarth_18
    5 years ago

    Hi elinevans ,

     

    You can create a measure with  below code and use it as filter on your visual

    filter_data = IF(MAX(ServiceInfo[StartDate]) <= MAX(date_table[Date]) && MAX(ServiceInfo[EndDate]) >= MIN(Date_Table[Date]),1,0)

    Output:-

    Thanks

elinevans's avatar
elinevans
Helper I
5 years ago

Thank you for your help Samarth_18 

 

In addition to this, if I wanted to report on these active clients e.g genders, could I apply this filter to all pages on the report so that only the gender data for the active clients show? 

 

At the moment I'm filtering by each individual gender type (and for other classifications) using the following (for a different data set)

genderanotherway = CALCULATE(count(Client[Gender]),filter(Client, Client[Gender]="Another Way"), FILTER(ServiceInfo, ServiceInfo[StartDate] <= MAX(DateTable[Date]) && ServiceInfo[EndDate] >= MIN(DateTable[Date])))
 
Thanks
Elin
 
Samarth_18's avatar
Samarth_18
Community Champion
5 years ago

Yes, either you can add gender condition into that filter measure or you can explicitly add gender column in the filter pane on your required visual.