Forum Discussion

Anonymous55's avatar
Anonymous55
Frequent Visitor
2 years ago
Solved

Filtering a DAX measure by a value in a separate table

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!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.