DAX compare number column from two filters

[Michal55]

Hi,

I'm struggling to do something in DAX that seems should be easy. I have a ProductFact table where I want to select
products which are in store 73 and also in the other estores.

I use this query which works fine.

EVALUATE
FILTER(
KM_ProductFact,
CONTAINS(FILTER(KM_ProductFact, KM_ProductFact[StoreID]=73), KM_ProductFact[ProductID], KM_ProductFact[ProductID])
)

result is

Now I want to compare the prices for products with other estores and find out which are cheaper than the primary estore(73).

I created this query
DEFINE
MEASURE KM_ProductFact[MyMeasure] = CALCULATE( AVERAGE( KM_ProductFact[Price] ), KM_ProductFact[StoreID] = 75)

EVALUATE
ADDCOLUMNS(
FILTER(
KM_ProductFact,
CONTAINS(FILTER(KM_ProductFact, KM_ProductFact[StoreID]=75), KM_ProductFact[ProductID], KM_ProductFact[ProductID])
),
"Nom. dif.", KM_ProductFact[MyMeasure] - KM_ProductFact[Price]
)

but a problem is that the new column "Nom. dif." has value only for store 75. Is not sliced by productID for every row and therefore there is minus result 0 - KM_ProductFact[Price]

result2

Correct result should be
"Nom. dif."
1 (203 - 202)
3 (203-200)
0 (203 - 203 )

Thank you for your help, because I don't have any idea how to continue.

Michal

 

[scottsen]

Your question is so well expressed and pretty with its screen shots, I feel like I really want to help you, but I am not sure I can. I just don't do much in your world of tabular and calling GENERATE and such.

I'll ask the one question that I suspect doesn't matter... in your "not working" version, you say

cheaper than the primary estore(73).

, but your formulas are using store 75. Just a bad screen shot as you debug?

 

[Michal55]

Thank you for your notice, but the store id was only misspelling when I wrote the post.


My other attempt was this

I was really happy that I solved the problem , but only on the test DB. On the production DB were more records and LOOKUPVALUE condition returns more value than 1 and
standard error appears "A table of multiple values was supplied where a single value was expected."




I thing that I am missing something important in DAX, because I only want to make a relation between two filters from one table where the key is ProductID

 

[scottsen]

this probably isn't help, but if we weren't in cool-guy-table-queries-in-tabular mode, and just straight dax in excel, I would have these measures:

AvgPrice:=average(ProductFact[Price])
Price75:=CALCULATE([AvgPrice], ProductFact[StoreID] = 75)
DeltaFrom75:=IF(ISBLANK([Price75]), BLANK(), [AvgPrice] - [Price75])

Any way to apply that to your world?

This does all seem a bit weird to me. This looks like a fairly typical product table -- I guess I would expect some filtering on MAX(Time) to get the lastest price point (vs averaging and such). You do need to sorta choose between those (avg prices or last price) to avoid your issues around "A table of multiple values was supplied"...

 

[Michal55]

I tested your tip with the measure. The result is similar which I sent before. The rows with different store id don't have price in the new column. There is no relation via ProductID

 

[scottsen]

To satisfy my own curiosity, ... what if you remove the CONTAINS clause?

 

[Michal55]

It doesn't work, this CONTAINS is basic for select only product which are also in other stores.

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

 

[Michal55]

Solution is there.
DAX ADDCOLUMNS

 

[scottsen]

Awesome! Ya, if Marco can't help you... nobody can Looked like the CONTAIN() wasn't need after all?

 

[Michal55]

It was simple variant. In my case I still use CONTAIN