The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
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
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:
Then retrieving the associated price:
then for a final value per product:
Or sales per date:
Hope it helps
@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,
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] )
)
)
User | Count |
---|---|
27 | |
10 | |
8 | |
7 | |
5 |
User | Count |
---|---|
33 | |
13 | |
11 | |
9 | |
8 |