locked
DAX ADDCOLUMNS RRS feed

  • Question

  • I haven't solved my problem yet, therefore I decided to describe it little bit better.

    I need to compose query which will add new column and the column will contain Price from store 75 for every product which estore 75 has and other estores have products also. When I will have this column I will be able to 
    compute differences and other statistics between stores and store 75.

    Result which I want.

    My best attempt was this query.

    DEFINE
    MEASURE ProductFact[MyMeasure] = CALCULATE( AVERAGE( ProductFact[Price] ), ProductFact[StoreID] = 75)

    EVALUATE
    ADDCOLUMNS(
    ProductFact,
    "Nom. dif.", ProductFact[MyMeasure] 
    )

    but the problem is that the price is shown only for store 75, but not at others stores.




    Other post and some answers, I have tested 3 other solutions.
    http://www.mrexcel.com/forum/powerpivot-questions/781770-dax-compare-number-column-two-filters.html
    http://social.technet.microsoft.com/Forums/sqlserver/en-US/dc0a3b01-af07-4efb-802d-51dfe97b17a4/cheaper-prices-for-estores?forum=sqlkjpowerpivotforexcel

    ps. When I finally find solution I add answer into the posts.

    Wednesday, June 4, 2014 11:35 AM

Answers

  • Then it depends on what you want to do when there are many rows - I used VALUES instead of AVERAGE because I (wrongly) assumed it was a constraint you had.

    Try this one:

    DEFINE
    MEASURE ProductFact[MyMeasure] =
        CALCULATE ( 
            AVERAGE ( ProductFact[Price] ),
            ALLEXCEPT ( ProductFact, ProductFact[ProductID] ),
            ProductFact[StoreID] = 75,
    )
    EVALUATE
    ADDCOLUMNS(
         ProductFact,
         "Nom. dif.", ProductFact[MyMeasure] 
    )


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    • Marked as answer by Michal Jezek Wednesday, June 4, 2014 4:29 PM
    Wednesday, June 4, 2014 4:15 PM

All replies

  • You should try this one:

    DEFINE
    MEASURE ProductFact[MyMeasure] =
        CALCULATE ( 
            VALUES ( ProductFact[Price] ),
            ALLEXCEPT ( ProductFact, ProductFact[ProductID] ),
            ProductFact[StoreID] = 75,
    )
    EVALUATE
    ADDCOLUMNS(
         ProductFact,
         "Nom. dif.", ProductFact[MyMeasure] 
    )


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Wednesday, June 4, 2014 3:14 PM
  • Marco, really  thank you for your response. Last night I read some post from your blog and began with excelent The Many-to-Many Revolution 2.0.pdf where I have tryed to find solution for my problem. 
    Your solution works almost perfect, but when the selected (primary) store has more than one product this error appears. 
    "alculation error in measure 'ProductFact'[MyMeasure]: A table of multiple values was supplied where a single value was expected."


    This is my testing table 



    I have changed store ID from 75 to 73 (more products) 


    DEFINE
    MEASURE ProductFact[MyMeasure] = 
        CALCULATE ( 
            VALUES ( ProductFact[Price] ), 
            ALLEXCEPT ( ProductFact, ProductFact[ProductID] ),
            ProductFact[StoreID] = 73
    )

    EVALUATE
    ADDCOLUMNS(
    FILTER(
    ProductFact,
    CONTAINS(FILTER(ProductFact, ProductFact[StoreID]=73 ), ProductFact[ProductID], ProductFact[ProductID])
    ),
         "Nom. dif.", ProductFact[MyMeasure] 
    )



    I have had the some problem when I tested this query 

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



    Thank you for your help.

         
    Wednesday, June 4, 2014 3:50 PM
  • Then it depends on what you want to do when there are many rows - I used VALUES instead of AVERAGE because I (wrongly) assumed it was a constraint you had.

    Try this one:

    DEFINE
    MEASURE ProductFact[MyMeasure] =
        CALCULATE ( 
            AVERAGE ( ProductFact[Price] ),
            ALLEXCEPT ( ProductFact, ProductFact[ProductID] ),
            ProductFact[StoreID] = 75,
    )
    EVALUATE
    ADDCOLUMNS(
         ProductFact,
         "Nom. dif.", ProductFact[MyMeasure] 
    )


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    • Marked as answer by Michal Jezek Wednesday, June 4, 2014 4:29 PM
    Wednesday, June 4, 2014 4:15 PM
  • I couldn't solve this problem for 10h. Thank you!


    Wednesday, June 4, 2014 4:31 PM