cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## SumProduct two tables with no relationship

I have two tables

 QuantityTable RefDate Product Quantity 31-Jan A 10 31-Jan B 20 31-Jan C 30 28-Feb B 100 28-Feb C 200 28-Feb D 300 31-Mar A 50 31-Mar D 70

 PriceTable EoMDate Product Price 31-Jan A 2.2 31-Jan B 3.2 31-Jan C 4.2 31-Jan D 5.2 28-Feb A 6.2 28-Feb B 7.2 28-Feb C 8.2 28-Feb D 9.2 31-Mar A 10.2 31-Mar B 11.2 31-Mar C 12.2 31-Mar D 13.2

These two tables have no relationship and are many to many with respect Date and Product

How does one calculate the following-->

SumX(QuantityTable[Quantity] * PriceTable[Price])

where QuantityTable[RefDate] == PriceTable[EomDate] && QuantityTable[Product] == PriceTable[Product]

In general I expect for all dates all products exist in PriceTable

5 REPLIES 5
Anonymous
Not applicable

In general you understanding is correct except

There is no 'unique' Product table and hence there is no join between Sales and Product Price and hence 'related'  does not work (actually Sales and Product are two Calculated Tables in a  measure with some appropriate filters), So lookup does not work

Resolver III

Hello @Anonymous ,

If you want to avoid relationship in your model I managed to find a work around implying to recreate a table with the price definition with the other one
This solution will find the latest price even not on the same date:

Table 2 = PriceTable
table 3 = QuantityTable

First re_creation of EOM in table 3:

Then retrieving the associated price:

then for a final value per product:

Or sales per date:

Hope it helps

Anonymous
Not applicable

Interesting that you managed to filter two tables without any relationship between them.

For me Filter(Table2, Table2[Product] == Table3[Product] && Table2[RefDate] >= Table3[EoMDate]) gives dax error

it does not show Table3 as a valid option to put on right side of the filter equation

Resolver III

Hey @Anonymous ,

Please Verify that you create the column in the right result table (table 3)
On the view of the data select the table 3 before click on crete a new column

Best regards,

Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

``````Sales measure: =
SUMX (
Sales,
Sales[Quantity]
* LOOKUPVALUE (
Product_Price[Price],
Product_Price[Product], RELATED ( 'Product'[Product] ),
Product_Price[EoMDate], RELATED ( 'Calendar'[Date] )
)
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors