Hello,
I have been working on this for a few weeks and I don’t understand why it’s not retrieving the correct calculation. I am hoping that you could help guiding me in the right direction.
I have a fact table as sample below:
| Customer | Customer Text | Ticket | Ticket Text | Priority | Category ID | Column 1 | Support | Reported On | Reported By | In Process | Resolved On | Closing | Status | Changed On | Changed By | Reported to resolved |
| 1234567 | Client 1 | 12345 | Ticket description | Normal | Cat 1_ID | Cat 1 | John Doe | 03-Jan-24 | Client 1 | 03-Jan-24 | 04-Jan-24 | 08-Jan-24 | Closed | 08-Jan-24 | John Doe | 1 |
| 1234567 | Client 1 | 12346 | Ticket description | Normal | Cat 2_ID | Cat 2 | Jane Doe | 15-Mar-23 | Client 1 | 16-Mar-23 | 22-Mar-23 | 24-Mar-23 | Closed | 24-Mar-23 | Jane Doe | 7 |
| 1234567 | Client 1 | 12347 | Ticket description | Urgent | Cat 3_ID | Cat 3 | John Doe | 05-Jan-24 | Client 1 | 08-Jan-24 | 09-Jan-24 | 15-Jan-24 | Closed | 15-Jan-24 | John Doe | 4 |
| 1234567 | Client 1 | 12348 | Ticket description | Urgent | Cat 1_ID | Cat 1 | Jane Doe | 05-Jun-23 | Client 1 | 05-Jun-23 | 08-Jun-23 | 03-Jul-23 | Closed | 03-Jul-23 | Jane Doe | 3 |
| 1234567 | Client 1 | 12349 | Ticket description | Normal | Cat 2_ID | Cat 2 | John Doe | 18-Jan-24 | Client 1 | 18-Jan-24 | | | In Process | 24-Jan-24 | John Doe | 0 |
| 1234568 | Client 2 | 12345 | Ticket description | Urgent | Cat 3_ID | Cat 3 | Jane Doe | 03-Jul-23 | Client 2 | 03-Jul-23 | 04-Jul-23 | 18-Aug-23 | Closed | 18-Aug-23 | Jane Doe | 1 |
| 1234568 | Client 2 | 12345 | Ticket description | Normal | Cat 1_ID | Cat 1 | John Doe | 24-Jan-24 | Client 2 | 24-Jan-24 | | | In Process | 25-Jan-24 | John Doe | 0 |
| 1234568 | Client 2 | 12345 | Ticket description | Normal | Cat 2_ID | Cat 2 | Jane Doe | 24-Jan-24 | Client 2 | 24-Jan-24 | | | Customer | 29-Jan-24 | Jane Doe | 0 |
From this table I need to know the average time between a “reported” ticket to a “resolved” ticket overall and by customer and by category last year (independently from status).
I started with adding a calculated column. Both in DAX and power query are retrieving the same result (whole or decimal number):
Attempt 1:
Reported to resolved =
DATEDIFF('Fact - All tickets'[Reported On], 'Fact - All tickets'[Resolved On], DAY))
Attempt 2:
Reported to resolved =
IF(ISBLANK('Fact - All tickets'[Resolved On]),0,DATEDIFF('Fact - All tickets'[Reported On], 'Fact - All tickets'[Resolved On], DAY))
When I put this formula to be evaluated in DAX studio, it gives me back an error: “the expression specified in the query is not a valid table expression”.
I thought I could reuse some previous post to get my answer, but I cannot apply the results (still gives me incorrect result):
https://community.fabric.microsoft.com/t5/Power-Query/Average-of-Datediff/m-p/2198025#M65025
https://community.fabric.microsoft.com/t5/Desktop/Average-of-Datediff-with-a-condition/m-p/3556960#M...
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Total-average-of-datediff/m-p/344728...
https://community.fabric.microsoft.com/t5/Desktop/Average-and-Median-DAX-calculating-incorectly/m-p/...
Then based on that I tried different formulas:
10 - Average reported to resolved 2023 =
AVERAGE(
'Fact - All tickets'[Reported to resolved]
10 - AverageX reported to resolved 2023 =
AVERAGEX(
SUMMARIZE(
'Fact - All tickets',
'Dim - Customer'[Customer Text],
"Average days to resolve", AVERAGE('Fact - All tickets'[Reported to resolved])),
[Average days to resolve]
)
Other option with same incorrect result:
10 - AverageX reported to resolved 2023 - values =
AVERAGEX(
VALUES('Dim - Customer'[Customer Text]),
CALCULATE([10 - Average reported to resolved 2023]))
I also tried another option with Calculate:
Test 5 = CALCULATE(AVERAGE('Fact - All tickets'[Reported to resolved]),ALL('Fact - All tickets'[Customer Text]))
I tried to test parts of the formulas in DAX Studio and both summarize and Values functions are retrieving correct data :
SUMMARIZE(
'Fact - All tickets',
'Dim - Customer'[Customer Text]
VALUES('Dim - Customer'[Customer Text])
I then divided my table only to get results for client 1 based on the following sample:
Customer | A | B |
Client 1 | 1.22 | 1 |
Client 1 | 6.87 | 7 |
Client 1 | 3.17 | 3 |
Test average =
AVERAGE(
'Table'[B]
)
The A column is the row result as I normally get the data in Date/time format from excel and then I remove the time which gives me rounding number which I can explain. The difference is not the result I get from my formulas though.
From this I do get the correct result: 3.65, whereas previous incorrect measures (tests) are giving the same result of 3.20.
Format of the column is whole or decimal number (both in power query editor and in the table view).
Relationship between fact table and customer dimension table is based on customer ID, many to 1 (single or both directions, I tried both) and customer ID is a text format in both table because I do receive some tickets with no customer ID (“#”) that I need to register as well.
Relationship between fact table and date table is based on inactive relationships (many to one, single) on all the dates and I use the “userelationship” function when needed in DAX, but no need here because I have a filter on the page with the date.
So I think something is wrong with my calculated column, but I am not sure how to dig into this problem and I do not understand where it could come from. Are you able to help please?
If you need more info on the data model, please let me know.
Thank you very much,
Cindy
