Need way to create a calc field from two tables but not able to create relation on two tables.

[Anonymous]

Hi I have two tables 1)Billings 2) Sales . I have shopid, materialid, invoicedate, qunatitypurchased on Billings table. I have shopid, materialid, invoicedate, quantitysold on Sales Table. 

I need to get Quantitypurchased - Quantitysold. But due to date field on these tables, they are having many to many relationship. I cannot create a relationship between these two tables. Is there a way I can acheive the calculation properly when I have date, shopid as slicers.  If I take slicer from table 1, then table 2 should also be filtered with correct quantities.

Issue - calculate a subtraction value from two fields  with slicers working on two tables.

Any help is appreciated!

Thanks

[Anonymous]

You have two fact tables, these should NOT be joined together.  You need dimensional tables that link to BOTH of these fact tables.  Specifically, you'll need a calendar table with a contiguous set of dates (even Saturdays and Sundays).  You'll also need a distinct list of MaterialIDs that will then have relationships to each fact table.  And one more table for ShopID, the same format as MaterialID (a distinct list of every ShopID from the 2 fact tables).

The slicers will need to come from these dimension tables.  That way the dimension tables will filter both fact tables properly.

Research data modeling and star schemas, this will help point you in the right direction.

Again, keep in mind you have 2 fact tables that will need to relate to each of your dimension tables.

Good luck

[Anonymous]

@Anonymous I really like your idea. I tried doing that for two dimension tables but I keep getting I cannot create active relationship or delete one of exisitng. Looks like it is creating a circular dependencies?  

[Anonymous]

Read this thread:

https://community.powerbi.com/t5/Desktop/Best-Way-for-work-with-Multiple-Fact-Tables/td-p/21441

search for this text:

"Edit 2 - mine is shown below"

That's what your data model needs to look like.  Put all of your dimension tables up top, and both of your fact tables below.

Also ensure that you're using SINGLE for cross-filtering behavior.  Do NOT use "BOTH".  That may be giving you the circular dependencies.

[Anonymous]

@Anonymous  This is going great so far.Thanks!  

I created single direction relationships and I was able to filter both fact tables by each dimension. However I have one more challenge on subtracting quantity from one fact table from another fact table. ( Facttable1[Qty Purchased] - Facttable2[Qty Sold]. When I pulled both the qty purchased and qty sold onto same visualization, I am not seeing values properly filtered out. Below are the sreenshots. If you see, the quantity Reported Sold is always 3 on first table chart while should be sliced properly based on dates like in second table chart.

[Anonymous]

@Anonymous Ignore my earlier post, I got it working after pulling date from the date dimension table instead of any fact table into the tablechart. but the other issue I have with slicers coming from different dimension tables -

Material ID coming from material dimension table

Shop ID coming from Shop Dimension table. 

But when I select a value in slicer for material ID, the values in Shop id slicer need to be filtered out as well. I am not able to acheive it as the slicers are coming from different dimension tables and are not related. Can you suggest a way out here.

[Anonymous]

@Anonymous, to get slicers from different tables to "listen" to each other, you need to turn the bi-directional filter on (BOTH as opposed to SINGLE).

However, you won't be able to do that because you have two fact tables, and you'll wind up with circular dependencies.

There could be another solution...you might be able to merge your two fact tables into one.  You have identical columns for invoicedate, ShopID, MaterialID.

Before we go down this path, is there only 1 record in your Sales table for each combination of ShopID, MaterialID, and InvoiceDate?  Or could Shop1 sell Material2 multiple times on InvoiceDate 5/24/2018.  Same question applies to Billings table.

Point is, you could add a step in Query Editor that merges the 2 tables together, joining on those three similar columns.  However, you may have to do some grouping / aggregating in Query Editor first so that the left outer join doesn't result in more rows than you're expecting (then you'd start doublecounting records...not good).