Filtering a DAX measure by a value in a separate table

[Anonymous55]

Hi!

I have a table ('Emails') which records info about all emails received by my company. This table has a column ('Time taken') which records how many days my company took to respond to the email. I previously wanted to calculate the percentage of emails which were responded to within 2 days. I used the following DAX, which worked great:

Value =

CALCULATE(

COUNTROWS('Emails') 'Emails'[Time taken] <= 2

) / COUNTROWS(Emails)

I now need to calculate the percentage of emails which were responded to within 2 *working* days. I have a separate table ('Dates') which has a column ('Working or non-working') that records whether a day is classed as a working or non-working day. A many-to-one relationship exists between 'Emails'[Date] and 'Dates'[Date].  Can anyone help me adjust the DAX above to achieve this?

For what it's worth, the 'Time Taken' column is a custom column which I created in Power Query. It's just a Date.Difference formula which calculates the days between two dates - maybe I need to tweak something here first?

I have tried a few things already with the help of ChatGPT which have either produced the following error message: 'The column 'Dates[Working or non-working]' either doesn't exist or doesn't have a relationship to any table available in the current context'; or given me 100%, which is incorrect. 

Thank you and please let me know if you need more info!

[Anonymous · Accepted solution]

Hi @Anonymous55 ,

Please try this DAX:

WorkingDaysCount = 
CALCULATE(
    COUNTROWS('Dates'),
    'Dates'[Working or non-working] = "Working",
    DATESBETWEEN('Dates'[Date], 'Emails'[ReceivedDate], 'Emails'[RespondedDate])
)

RespondedWithin2WorkingDays = 
CALCULATE(
    COUNTROWS('Emails'),
    [WorkingDaysCount] <= 2
)

PercentageRespondedWithin2WorkingDays = 
DIVIDE(
    [RespondedWithin2WorkingDays],
    COUNTROWS('Emails'),
    0
)

If this not works, can you provide some sample data?

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

[DataInsights]

@Anonymous55,

One option is to move your Time taken column from Power Query to DAX, using this calculated column in table Emails:

Time taken = 
VAR vReceivedDate = Emails[Received Date]
VAR vRespondedDate = Emails[Responded Date]
VAR vDaysBetween =
    DATEDIFF ( vReceivedDate, vRespondedDate, DAY )
VAR vNonWorkingDays =
    COUNTROWS (
        CALCULATETABLE (
            Dates,
            Dates[Date] >= vReceivedDate,
            Dates[Date] <= vRespondedDate,
            Dates[Working or non-working] = "Non-working"
        )
    )
VAR vResult = vDaysBetween - vNonWorkingDays
RETURN
    vResult

In this example, there is no relationship between the tables. Your measure should work with this column.