Creating a Summary table from two fact tables

[BrentHamm]

I am struggling with the dax to create a summary table (in red box) from a purchase and invoice table as shown below.

The data needs to be in this format for the venn digram to function.  I also need the slicer to function.

Basically am looking to see what accounts have invoices but no purchases, and the other way around.  If an account has multiple purchases or invoices, it does not matter, still only counted as 1.

I have attached the PBIX, and honestly have spent more time on this than i care to admit!

PBIX Here 

Thanks!

[BrentHamm]

l the Venn Diagram i used there is very specific on the format of data it needs.  Not sure how i could make it draw from the the other tables with out having a summary.

[parry2k]

@BrentHamm I'm not sure why you think you cannot achieve without creating a summary table. Anyhow, you already have a solution, if you are ok with it, you are good. You always want to think about how any solution will scale and perform when you are working with a large dataset. Good luck.!

[parry2k]

@BrentHamm what is a counter? You don't need to create an extra summary table to achieve this, the recommendation would be to create measure without adding extra table in the model.

[BrentHamm]

The Venn diagram i am using is very specific requirements.  I see no way to make it work with out an extra table.

However, would love a solution that did not need it.

[StefanoGrimaldi · Accepted solution]

try something like this:

summary table = SUMMARIZE(Accounts,Accounts[ID],"HasInvoice",IF(CALCULATE(COUNT(Invoices[Invoice ID]))>0,1,0),"haspurchase",IF((CALCULATE(COUNT(Purchases[Purchase ID])))>0,1,0))

then add a column as follow for the counter on the new table: 

counter = if(and(summary table[ hasinvoice]=1,summary table[haspurchase]=1),1,0)

if this helped solve your question please masrk as solution, and if if like the solution give kudos 🙂 

[BrentHamm]

That is exactly what i wanted, and i was close.  Thanks so much!!