DAX pick a value from tied resultset

0
votes

i need help with the following dax statement.

Situation: I have 2 tables. One table contains sell data with articleIDs, dateIDs and sell prices, another table contains stock movements data with articleIDs, dateIDs and purchase prices. According to the dateID i want to write the purchase prices into the first table using a calculated column because i need the prices for every row.

Example:

Table1 t1

  • t1.articleID = 123; t1.dateID = 20160905; t1.sellPrice = 62,55; t1.purchasePrice = My DAX Statement

Table2 t2

  • t2.articleID = 123; t2.dateID = 20160905; t2.purchasePrice = 37,07
  • t2.articleID = 123; t2.dateID = 20160905; t2.purchasePrice = 37,07
  • t2.articleID = 123; t2.dateID = 20160906; t2.purchasePrice = 37,07
  • t2.articleID = 456; t2.dateID = 20160905; t2.purchasePrice = 12,15

My DAX Statement:

= CALCULATE (
    VALUES (t2[purchasePrice]);
        TOPN (
            1; 
            FILTER(FILTER(t2; t2[articleID] = t1[articleID]); t2[dateID] <= t1[dateID]); t2[dateID]; DESC
                  )
        )

With my DAX Statement i get the following error: A table of multiple values was supplied where a single value was expected.

It is normal that i have more than one row matching in the table 2. Actually I just want the price of any of them on the corresponding dateID, even if they are tied. So i used the TOPN function with the value 1 and sorted by date but the error still remains. Is there a way to fix my DAX Statement to achieve this?

1
filtercalculated-columnsdax

1 Answers

0
votes

Create a calculated column in T1 and use this expression:

purchasePrice =
CALCULATE (
    MAX ( T2[purchasePrice] ),
    FILTER ( T2, T1[ArticleID] = T2[articleID] && T1[DateID] = T2[dateID] )
)

Note I use comma to separate passed arguments to the functions but I see in your expression you used semicolon. Change it to match your system list separator.

It is not tested but should work. Let me know if it works for you.