Count results of a measure based on a condition, filter another table

[Elisa112]

Hi Experts

I am struggling to count the results of a measure which counts the number of days between two meeting dates, I need to find occurences 10 days or over and then find the percentage. The dates are in a summarised table based on a meetings table. I have used the following measures, which do not bring back the correct results as I need to bring the results back for active users only, however the status information is in another table (Users).  Yhe measures I have used so far are

1. count Days test = Countx(filter(values('DatesSummaryTable'[2nd_to_3rd_Meeting]) ,[2nd_to_3rd_Meeting] > 10), [2nd_to_3rd_Meeting])

2. Count Days test = CALCULATE(DISTINCTCOUNT('DatesSummaryTable'[2nd_to_3rd_Meeting]),FILTER('DatesSummaryTable',[ 2nd_to_3rd_Meeting]> 10 ))

These measures do work in their own right but I need to filter to only count active users which are in another table and then find the percentage of meetings with days over 10.

Here is an axample of the tables in the model:

User Table and summarised meeting dates table (taken from a meeting table and added a calculated column)

I want to create a card visual showing the number of meetings over 10 (active users)and then a percentage from that.

Thank you in advance for guidance

[Anonymous · Accepted solution]

Hi @Elisa112 ,

Based on your description, I created the following model. Refer to the following formula:

Create the following calculated column to find the date difference.

Days Between Meetings = 
DATEDIFF(
    'DatesSummaryTable'[MeetingDate1], 
    'DatesSummaryTable'[MeetingDate2], 
    DAY
)

then create below measure to get result:

Total Meetings for Active Users = 
CALCULATE(
    COUNTROWS('DatesSummaryTable')
)

Count Days Over 10 for Active Users = 
CALCULATE(
    COUNTROWS('DatesSummaryTable'),
    'DatesSummaryTable'[Days Between Meetings] > 10
)

Percentage of Meetings Over 10 Days = 
DIVIDE(
    [Count Days Over 10 for Active Users],
    [Total Meetings for Active Users],
    0
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Elisa112]

Hi @Anonymous

Sorry for delay in responding, I did not realise I had a response, going to try this now and let you know.

Thanks so much