Solved: I have no idea to solve this issue related to DAX....

Nate · ‎06-19-2018

Guys, I have been trying to solve this issue for whoe day.

but unfortunately, I coud not find the answer. could you help me to solve this one?

1. Table information

(1) A [Sales] - Product ID, SalesDate,sales count

(2) B [Customer Complain] - Product ID, ComplainDate, inbound call count

(3) C [Issue management] - Product ID, Issue_createdate, Issue_resolvedDate

2. Calcuration field

(1) Contact rate = Sales count / Inbound call count

3. FYI

(1) We have a lot of inbound complain call from consumer everyday. if there is serious or important issue, we just change the priority complain to issue. Our issue management team handle that issue by themself

(2) we want to check issue management team performance. for this, I would like to compare Before vs After contact rate by issue_resolvedDate. if before contact rate is 34% and after contact rate is 12%. it means our issue management is successful.

3. Question

(1) I'd like to know the 'contact rate' data based on issue_resolveddate by each product_ID

how can i make this number by DAX or whatever.

could you help this one?

v-yuta-msft · ‎06-20-2018

Hi Nate,

Based on your description, all the three tables are based on column [Product ID], right? As a general solution, DAX pattern below may be for your reference and check if it can work:

Contact rate =
CALCULATE (
    COUNT ( 'Sales'[Sales] ) / COUNT ( 'Customer Complain'[inbound_call] ),
    ALLEXCEPT (
        'Issue management',
        'Issue management'[Issue_resolvedDate],
        'Issue management'[Product ID]
    )
)

In addtion, if you still need help, could you please give more details about your tables and expected result?

Regards,

Jimmy Tao

View solution in original post

Nate · ‎06-19-2018