Join Calculated tables DAX

[davijs]

Hi -   

I am trying to Join 3 calculated tables, each with information about defects on an Application/Function, where the lookup table (Application Inventory) is related to each table, but it is a many - many between each of the tables. I was looking at other solutions, and I have seen the "CountRows" function used, but I need to SUM The number of Open, Resolved, Injected defects by Function into one calculated table.

The final result would look like:

Function    Open      Injected    Closed (and Ultimately)   Open+ Injected - Closed

 App 1          2              1                  1                                              1

App 2           5              0                  2                                              3 

App 3           10             2                 8                                              4 

Thanks for your help!

[Greg_Deckler]

Hmm, perhaps we need to backup and discuss your original raw data that you have as my spider sense is tingling about why you need 3 calculated tables in addition to your raw data. 

[davijs]

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

[davijs]

BTW - 

Below is the model for the source data:

[Greg_Deckler · Accepted solution]

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.