Forum Discussion
Creating a Summary table from two fact tables
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!
Thanks!
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 🙂
6 Replies
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 🙂That is exactly what i wanted, and i was close. Thanks so much!!
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.
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.
