Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Anonymous
Not applicable

SumProduct two tables with no relationship

I have two tables

 

QuantityTable
RefDateProductQuantity
31-JanA10
31-JanB20
31-JanC30
28-FebB100
28-FebC200
28-FebD300
31-MarA50
31-MarD

70

 

 

PriceTable
EoMDateProductPrice
31-JanA2.2
31-JanB3.2
31-JanC4.2
31-JanD5.2
28-FebA6.2
28-FebB7.2
28-FebC8.2
28-FebD9.2
31-MarA10.2
31-MarB11.2
31-MarC12.2
31-MarD13.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

Hi Jihwan_Kim, thank you for your reply.

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

philouduv
Resolver III
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:
philouduv_0-1670945447288.png


Then retrieving the associated price:

philouduv_1-1670945473101.png

 

then for a final value per product:
philouduv_2-1670945554001.png


Or sales per date:

philouduv_3-1670945718224.png


Hope it helps



Anonymous
Not applicable

@philouduv thank you for your reply. 

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

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,

Jihwan_Kim
Super User
Super User

Hi,

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

 

Jihwan_Kim_1-1670943548397.png

 

 

Jihwan_Kim_0-1670943527343.png

 

 

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.


Go to My LinkedIn Page


Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors