+ Reply to Thread
Results 1 to 5 of 5

Conversion of formula to DAX / PowerPivot

  1. #1
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    54

    Exclamation Conversion of formula to DAX / PowerPivot

    Hi,

    I am looking to convert an Excel formula to a DAX formula to be used in my PowerPivot data model.

    My data set lists a number of investors and their portfolios' allocation towards different stocks.

    Unfortunately, it is formatted something like this: Stock 1/5%/500,25:Stock 2/10%/1000,50:Stock 3/85%/8004, with "Stock name / proportion / amount", where the amount is irrelevant.

    Currently, my Excel solution is along the lines of the following and can be found in the table "Solution":

    HTML Code: 
    =SUMPRODUCT(TEXT(SUBSTITUTE(MID($B3;FIND("^^";SUBSTITUTE($B3;$D$3:$D$14;"^^")&"^^")+LEN($D$3:$D$14&"/");2);"%";"");"0;0;0;\0")*($E$3:$E$14=E$18))
    Converting each formula into DAX is simple (replacing the TEXT formula with the FORMAT formula). However, my problem is that I am not sure how to convert the column references to table "Type" into DAX.

    I have tried with the ALL function, but without luck. So, the parts I need help fixing are the " 'Type'[Type] " parts (I have removed some of the extra code that has nothing to do with the issue):

    HTML Code: 
    =FORMAT(SUBSTITUTE(MID('Portfolios'[Stocks, proportion, and amount];FIND("^^";SUBSTITUTE('Portfolios'[Stocks, proportion, and amount];'Type'[Type];"^^")&"^^")+LEN('Type'[Type]&"/");2);"%";"");"0;0;0;\0")
    I have previously been using a Power Query solution, but unfortunately the workbook has begun crashing occasionally due to the size of the real data sets and number of columns the PQ procedure runs through.

    The sample workbook is attached here. Thanks!
    Attached Files Attached Files
    Last edited by Phil123456789; 11-23-2020 at 09:57 AM.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Conversion of formula to DAX / PowerPivot

    I really wouldn't recommend doing this in DAX. As DAX gets re-evaluated with each interaction with the pivot table.

    It will significantly slow down your performance.

    Try following attached sample.
    Attached Files Attached Files
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    54

    Re: Conversion of formula to DAX / PowerPivot

    Thanks @CK76.

    I realize DAX probably is not the optimal solution.

    But, my current procedure includes a few more steps than yours because the amount of stocks in each portfolio can range between 0 and 25, and as there are two different columns with the bad formatting (one for existing savings, one for current contributions).

    Altogether, it not only makes the Power Query refresh time VERY long (+ 2 minutes), it also leads to frequent crashes of the workbook.

    Therefore, I was hoping to test a DAX version of the solution. The pivot table is pretty static, so I suspect it may be a better option in this particular case.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Conversion of formula to DAX / PowerPivot

    Then I'd recommend following method.

    1. Add index column to your original table. Split into 2 separate tables, each having 1 column of strings. Make sure to carry over index column to each.
    2. Create separate dimension table to relate the two tables using Index.
    3. Perform transformation on both tables.
    4. Then create Star Constellation Schema in data model.

    This will be much more performant than DAX calculated columns. Number of stocks in each portfolio should not have too much impact on number of steps required.
    By splitting fact table into two individual part, it will simplify transformation required and will reduce memory foot print.

    DAX isn't really meant for this sort of calculation. If still having issues, I'd recommend uploading sample that is more representative of your actual data set. That way we can help you find optimized solution that will meet your need.

    EDIT: Oh, if you have source data residing in the workbook with PowerQuery. I'd recommend separating them out. And query external workbook using PQ.
    Last edited by CK76; 11-23-2020 at 11:06 AM.

  5. #5
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    54

    Re: Conversion of formula to DAX / PowerPivot

    Thanks, makes sense. Definitely will look into that option.

    The actual data set is almost identical to the sample set, except there is another (identical) column next to the current "stock column", that both columns may contain more / less than 3 stocks, and that the number of investors is of course much larger. Of course, the stock names are replaced by actual stocks, but with a related lookup table categorizing the regions exactly like in the sample.

    Data source is indeed in the actual workbook, since it is simpler for the end user. Tried moving it to external workbook, and improved refresh rate by about 30%, so great suggestion.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Allow use of Slicers on PowerPivot to be used by users without PowerPivot
    By weeble33 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-09-2017, 05:19 PM
  2. [SOLVED] If formula for powerpivot
    By stephme55 in forum Excel General
    Replies: 2
    Last Post: 06-23-2016, 11:57 AM
  3. Powerpivot formula help
    By Jo2710 in forum Excel General
    Replies: 0
    Last Post: 06-07-2016, 03:54 AM
  4. Is it possible to use Cube Formula without Powerpivot?
    By liquidmettle in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 11-30-2015, 01:31 PM
  5. Replies: 4
    Last Post: 07-23-2015, 03:35 PM
  6. Replies: 2
    Last Post: 06-02-2014, 09:34 AM
  7. Replies: 0
    Last Post: 11-20-2012, 05:34 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1