Forum Discussion
Join Calculated tables DAX
OK, what I would suggest is that you use an Append query to append all 3 of your tables together. You could add a column in each one of them to tell you where it came from, just create the same column in all three source queries with the same column name and give them a static value of "Open", "Closed", etc.
I think then your problems will become far easier and almost trivial at that point.
That is a great Question!
So, my sources are from three different excel files kept on SharePoint.
(a) Open incidents
(b) Closed incidents
(c) Injected incidents
I built calculated tables, knowing that this is probably not my final solution, but an understandable one for me. The code is similar for all three, so I have included one below:
Closed by Week-Function = CalculateTable (
SUMMARIZE (
'Service Desk Ticket - Closed',
'Service Desk Ticket - Closed'[Function],
"Closed", COUNT('Service Desk Ticket - Closed'[Request ID])
) ,
FILTER(All('Service Desk Ticket - Closed'[CAB Week Number]), 'Service Desk Ticket - Closed'[CAB Week Number] < MAX('Week'[CAB Week Number])))
Open is exactly the same, Injected is a SUM (injected column).
So, backing up might very well be useful. I have been struggling with this problem for a few days now and that is why I broke it down and asked the question. Of course, if you would like to back up I would be MORE than thankful.
Joy
BTW -
Below is the model for the source data:
OK, what I would suggest is that you use an Append query to append all 3 of your tables together. You could add a column in each one of them to tell you where it came from, just create the same column in all three source queries with the same column name and give them a static value of "Open", "Closed", etc.
I think then your problems will become far easier and almost trivial at that point.