locked
Select top 3 sales with ties for product and display only sales numbers RRS feed

  • Question

  • Dear Team,

    I am trying to select top 3 sales with ties for product and display only sales numbers. Is this efficient code:

    EVALUATE
    SELECTCOLUMNS (
        TOPN (
            VALUES ( disTop[Top] ),
            CALCULATETABLE ( fSales, dProduct[Product] = VALUES ( disCriteria[Product] ) ),
            fSales[Sales]
        ),
        "Sales", fSales[Sales]
    )
    ORDER BY [Sales] DESC

    The result I get is correct:

    Sales
    1957.44
    1821.34
    1715.38
    1715.38

    I am just wondering if the construction of the formula and placement of functions like SELECTCOLUMNS is efficient.

    Sincerely, Mike Girvin

    Tuesday, June 6, 2017 7:07 PM

Answers

  • Hi Mike,

    Thanks for your question.

    Based on my experiances, this DAX formula should be efficent, you have minimized the table by filtering product. You can also try below DAX formula:
    EVALUATE
    TOPN (
        VALUES ( disTop[Top] ),
        SELECTCOLUMNS (
            CALCULATETABLE ( fSales, dProduct[Product] = VALUES ( disCriteria[Product] ) ),
            "Sales", fSales[Sales]
        ),
        [Sales]
    )
    ORDER BY [Sales] DESC


    Best Regards
    Willson Yuan
    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

    Wednesday, June 7, 2017 6:06 AM

All replies

  • Mike,

    Well! First of all thank you very much for your huge personal contribution to the Excel community. Basically, I have to thank you for my job in a way.

    Regarding SELECTCOLUMNS(), It is my preferred choice over ADDCOLUMNS() when I want to return calc column or columns from my query.  

    Just  a few comments on the code in case if you plan to run this in Power BI.  I see you have 2 "dis" tables I assume these are disconnected tables. Now, if you are generating  TOPN n_value with a disconnected table, the way you have it set up now VALUES ( disTop[Top] ) this will break your code in case nothing is selected on your slicer (If you were able to successfully run your query this means in your disTop now you have only one value ) .  As a rule, we usually create a separate measure with HASONEVALUE() and call a measure in.
    As for disCriteria[Product]  would simply create a slicer from the Product dimension.

    By the way, you plan to use this in query only ?

    Good luck
    N -



    Wednesday, June 7, 2017 4:13 AM
  • Hi Mike,

    Thanks for your question.

    Based on my experiances, this DAX formula should be efficent, you have minimized the table by filtering product. You can also try below DAX formula:
    EVALUATE
    TOPN (
        VALUES ( disTop[Top] ),
        SELECTCOLUMNS (
            CALCULATETABLE ( fSales, dProduct[Product] = VALUES ( disCriteria[Product] ) ),
            "Sales", fSales[Sales]
        ),
        [Sales]
    )
    ORDER BY [Sales] DESC


    Best Regards
    Willson Yuan
    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

    Wednesday, June 7, 2017 6:06 AM
  • Wow, I can't believe how I keep missing posts from this forum. I am just seeing this 21 days later... : (

    Nevertheless, I am super happy that the videos and files I post are helpful to you.

    Thank you for your helpful comment. I was actually writing this in Excel in Edit DAX window the the disTable is a single cell with criteria from Excel. Your comment about HASONEVALUE is good for the other situations when there are more than just one value. Thanks!
    Wednesday, June 28, 2017 11:14 PM
  • Thank you for your comment. I am just seeing this 21 days later... : (  

    You have SELECTCOLUMNS inside TOPN and around CALCULATE table. Does selecting columns before running TOPN make it more efficient?

    Sincerely, Mike Girvin
    Wednesday, June 28, 2017 11:20 PM
  • Do you know how I can notified when people answer my questions? I am not getting notified. I also thought I checked back to see if this post was answered after June 7, but I guess I did not...
    Wednesday, June 28, 2017 11:21 PM
  • I figures out how to get alerted. Of course it was right under my nose...

    Sincerely, Mike Girvin

    Wednesday, June 28, 2017 11:31 PM
  • Hi Mike,

    Thanks for your response.

    There is no big difference for your and my DAX formula. The most important thing is the CALCULATE table, as we are all trying minmize the table records throgh CALCULATE table formula first.


    Best Regards
    Willson Yuan
    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


    Thursday, June 29, 2017 7:08 AM
  • Hi Mike,

    if any of the answers above has solved your problem, please mark is as answer. That way the thread will be closed and others can find suitable answers for similar problems quicker. Thx.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, August 6, 2017 8:00 AM
    Answerer