locked
SWITCH optimization RRS feed

Answers

  • Hi Bartek,

    the reason why your effort do not succeed is the way you use  'Internet Sales'[Order Quantity] and the content of that column in the various years. You end up filtering the denominator in [vs PY Sales] with distinct values from the following year and those values do not exist in the denominator year.

    Do the following:

    Create a calculated column in the 'Internet Sales' table with the value 1 (one) like this …

    theNumberOne = 1

    Then use that column instead of 'Internet Sales'[Order Quantity] in the FILTER/IF parts of the measure and you should be good to go.

    If you use the DAX engine from power BI Desktop/service, Analysis services 2016 or power pivot 2016 I doubt the trick showed by Marco will help speed up the calculations, however I would love to hear about it if it turns out to be so. The DAX engine in the above mentioned versions is vastly improved compared to the engine existing when Marco wrote the blog post.

    Best regards.

    Jes.




    • Edited by jessamhan Monday, November 28, 2016 8:25 AM
    • Marked as answer by Bartek Wachocki Wednesday, November 30, 2016 9:07 AM
    Monday, November 28, 2016 8:23 AM

All replies

  • Should not you write CALCULATE([vs PY Sales],... as this is the name of the measure that appears in the SWITCH statement ? 
    Wednesday, November 16, 2016 1:11 PM
  • Yes Bertrand. You are right.

    Although it is still not working.

    I am sharing my test file. Will be easier

    https://www.dropbox.com/s/zxdpek27ww6oro1/SWITCH%20optimization.xlsx?dl=0


    Wednesday, November 16, 2016 2:27 PM
  • Sorry, no access to Dropbox from office.

    Does the measure [vs PY Sales] provide correct results when you select it in a pivot table ? Does the [Sales] (non Switch version) measure provide correct results when you select "MTD" or "YTD" ?

    If this is the case, then you may check if there is a typo in "vs PY" (two spaces ?). Check the string returned by [Selection] when you select "vs PY".

    Wednesday, November 16, 2016 3:06 PM
  • Bertrand

    [vs PY Sales]  is correct. Works well as a standalone measure.

    For (non SWITCH) version  I have already "Current" filter working.

    "MTD" or "YTD" are not ready yet with lower priority.

    I got error in my pivot not in dax.

    BTW. You should have opened dropbox file even if you haven't got an account.

    Just open the link and on the bottom of pop-up window click something like:

    "no thanks, continue..."

    if it is not working refresh URL

    Wednesday, November 16, 2016 3:25 PM
  • Guys, 

    If something is not clear I can provide more explanations. 

    Thanks in advance

    Thursday, November 17, 2016 4:39 PM
  • Hi Bartek,

    There are issue in the measure vs PY Sales. When the year is 2013, it will return error, because there is no 2012's data in filter. I change the measure as the following formula, it works fine. So the syntax of sales measure is right.



    vs PY Sales:=[Internet Total Sales]/CALCULATE([Internet Total Sales],FILTER(ALL('Internet Sales'[Year]),'Internet Sales'[Year]=MAX('Internet Sales'[Year])))-1

    I suggest you change your [vs PY Sales] measure, please review this article. I will post the updates if I find better solution.

    If you have any question, please feel free to ask. 

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 18, 2016 8:04 AM
  • Hi Angelina

    Thanks. I appreciate your help. I have tested your suggestion and have a questions.

    First of all I have renamed your vs PY Sales  to vs PY Sales (Angelina)  to differentiate your measure vs my original measure. After my test I received pivot table like this: 

    And here is a dax:

    vs PY Sales :=
        [Internet Total Sales]
            / CALCULATE (
                [Internet Total Sales],
                FILTER (
                    ALL ( 'Internet Sales'[Year] ),
                    'Internet Sales'[Year]
                        = MAX ( 'Internet Sales'[Year] ) - 1
                )
            )
            - 1
    vs PY Sales (Angelina) :=
        [Internet Total Sales]
            / CALCULATE (
                [Internet Total Sales],
                FILTER (
                    ALL ( 'Internet Sales'[Year] ),
                    'Internet Sales'[Year] = MAX ( 'Internet Sales'[Year] )
                )
            )
            - 1
    sales:=CALCULATE (
            [Internet Total Sales],
            FILTER (
                VALUES ( 'Internet Sales'[Order Quantity] ),
                'Internet Sales'[Order Quantity]
                    = IF ( [Selection] = "Current", 'Internet Sales'[Order Quantity], -1 )
            )
        )
            + CALCULATE (
                [vs PY Sales (Angelina)],
                FILTER (
                    VALUES ( 'Internet Sales'[Order Quantity] ),
                    'Internet Sales'[Order Quantity]
                        = IF ( [Selection] = "vs PY", 'Internet Sales'[Order Quantity], -1 )
                )
            )

    Let's put aside 2013. it is not a problem for me.

    Could you please have a cross check because I am not sure how you get your results?




    Friday, November 18, 2016 9:30 AM
  • Hi Bartek,

    Sorry for inconvenience, changeing your measure is just to verify the syntax of your sales measure is corrent. In the desired pivot table, you want to calculte the percentage of current year vs previous year for each category, right? For example, it will calculate 17(PC of 2014)-12(PC of 2013)/17, right? If it is, the measure will return multiple result because 2014 vs 2013, 2015 vs 2014 and so on.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 21, 2016 10:38 AM
  • Ok, I will explain more clearly. 

    I have already my "vs PY Sales" measure which works fine as a standalone one. See (1) on the below screenshot.

    But if I use this measure inside "sales (Bartek)" measure it gives me #NUM! errors in my pivot. See (2). 

    I gave my original "sales" measure a new name "sales (Bartek)" to differentiate it vs "sales (Angelia)"- the one proposed by Angelia. 

    I am wondering why my "vs PY Sales" works as a standalone but does not work inside "sales (Bartek)"?

    Here is an excel file with this view:

    https://www.dropbox.com/s/7db6w51rfl6a72q/SWITCH%20optimization%20%282%29.xlsx?dl=0

    @Angelia, answering your questions. The correct calculations should look like: (17(PC of 2014) / 12(PC of 2013)) -1 . The measure should able to filter categories and its total. For instance total sales of 2016 / total 2015 should give 21%.

    How to do my "sales (Bartek)" measure working fine?

    Monday, November 21, 2016 12:40 PM
  • Hi Bartek,

    Thank you for detail description, the "vs PY Sales" is correct. After tested, there must be some issue in optimiztation SWITCH funtion.

    I create the measure using SWITCH function as the following formula.

    sales (Test):=SWITCH(
     VALUES(Period[Period]),
      "Current", [Internet Total Sales],
    "vs PY",[vs PY Sales],
    -1
     )
    And create a pivot table. From the screenshot below, you can see that the "sales(Bartek)" column(highlighted in black box) is incorrect, the sales(Test) is right when we click "Current" in slicer.



    When we click the "vs PY", the sales(Test) works fine.



    Best Regards,
    Angelia 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 22, 2016 9:56 AM
  • Thanks Angelia

    Everything you wrote is correct. But I am looking how to optimize the syntax with SWITCH based on Marco Russo pattern. My original "Sales" measure... 

    ...works with poor performance. For instance processing time when I click a slicer exceed 5sek sometimes.

    It is too slow for me. So I decided to try Marco solution:

    http://sqlblog.com/blogs/marco_russo/archive/2014/08/18/possible-switch-optimization-in-dax-powerpivot-dax-tabular.aspx

    My case is similar to this described under the link with one difference. I need to add [vs PY Sales] measure.

    Here comes the issue. Marco Russo's pattern doesn't work with [vs PY Sales] or needs modifications.

    I drew this conclusion when I created "sales (Bartek)" measure:

    sales (Bartek):=CALCULATE (
            [Internet Total Sales],
            FILTER (
                VALUES ( 'Internet Sales'[Order Quantity] ),
                'Internet Sales'[Order Quantity]
                    = IF ( [Selection] = "Current", 'Internet Sales'[Order Quantity], -1 )
            )
        )
            + CALCULATE (
                [vs PY Sales],
                FILTER (
                    VALUES ( 'Internet Sales'[Order Quantity] ),
                    'Internet Sales'[Order Quantity]
                        = IF ( [Selection] = "vs PY", 'Internet Sales'[Order Quantity], -1 )
                )
            )

    This is my implementation of Marco Russo technique but it doesn't work.

    You can find an excel in previous posts with this


    Wednesday, November 23, 2016 10:38 AM
  • Hi Bartek,

    the reason why your effort do not succeed is the way you use  'Internet Sales'[Order Quantity] and the content of that column in the various years. You end up filtering the denominator in [vs PY Sales] with distinct values from the following year and those values do not exist in the denominator year.

    Do the following:

    Create a calculated column in the 'Internet Sales' table with the value 1 (one) like this …

    theNumberOne = 1

    Then use that column instead of 'Internet Sales'[Order Quantity] in the FILTER/IF parts of the measure and you should be good to go.

    If you use the DAX engine from power BI Desktop/service, Analysis services 2016 or power pivot 2016 I doubt the trick showed by Marco will help speed up the calculations, however I would love to hear about it if it turns out to be so. The DAX engine in the above mentioned versions is vastly improved compared to the engine existing when Marco wrote the blog post.

    Best regards.

    Jes.




    • Edited by jessamhan Monday, November 28, 2016 8:25 AM
    • Marked as answer by Bartek Wachocki Wednesday, November 30, 2016 9:07 AM
    Monday, November 28, 2016 8:23 AM
  • Thanks

    I will do tests. I will go back soon. 

    I am using power pivot 2013. 

    Wednesday, November 30, 2016 8:37 AM
  • @ Jes

    It is working. You were right. Based on your suggestion I made "sales (Bartek)_v2" and here is a result:

    and the measure:

    sales (Bartek)_v2:=CALCULATE (
            [Internet Total Sales],
            FILTER (
                VALUES ( 'Internet Sales'[Order Quantity] ),
                'Internet Sales'[Order Quantity]
                    = IF ( [Selection] = "Current", 'Internet Sales'[Order Quantity], -1 )
            )
        )
            + CALCULATE (
                [vs PY Sales],
                FILTER (
                    VALUES ('Internet Sales'[thnumberone]),
                    'Internet Sales'[thnumberone]
                        = IF ( [Selection] = "vs PY", 'Internet Sales'[thnumberone], -1 )
                )
            )
    As far performance. I must test it on my real data (not a sample). I will share later my findings



    Wednesday, November 30, 2016 9:06 AM
  • Hi Bartek, good to hear it helped you.

    Have a nice weekend.

    Friday, December 2, 2016 2:12 PM