Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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) defined by a slicer on the report. The slicer is based off a date table created by using CALENDARAUTO(). So essentially I wanted to find how many clients have a start date before y AND end date after x. Where x and y can be altered by a date slicer. I ended up doing this using the following formula:
The next step is how do I find what are the client references of the clients within that count?
In this example if the date slicer was between 1st May 2020 and 3rd June 2020 the list would be 1,4,5 but how do I find that automatically?
Thanks
Solved! Go to Solution.
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
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
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
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
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)
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.
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |