Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Excel Formula Translation to DAX

  • 1.  Excel Formula Translation to DAX

    Silver Contributor
    Posted Feb 10, 2019 01:50 PM
      |   view attached

    HI All

     I am new to DAX could you please help me convert below written Formula in Excel Workbook to DAX. (as we are migrating to Power BI). Please find the sample excel file as an attachment named EWR LP. (The formula is written on column L, Just place your cursor on cell L9 to see he formula in the attached excel file)

     I provide the details for referenced dates used in below excel formula

     Plan Start = 08/12/2018

    Plan Finish = 04/01/2019

     Here is the formula written in Excel Workbook 

    =IF(
        AND(
            MAX(
                E258,
                F258
            ) <= PlanFinish,
            MIN(
                E258,
                F258
            ) >= PlanStart
        ),
        COUNT(
            E258:F258
        ),
        IF(
            AND(
                E258 >= PlanStart,
                E258 <= PlanFinish
            ),
            1,
            IF(
                AND(
                    F258 >= PlanStart,
                    F258 <= PlanFinish
                ),
                1,
                0
            )
        )
    )







    ------------------------------
    Zahid Shaikh
    9152245089
    ------------------------------

    Attachment(s)

    xlsx
    EWR LP.xlsx   112K 1 version


  • 2.  RE: Excel Formula Translation to DAX

    Posted Feb 11, 2019 07:53 AM
    Hi,

    Declare two measures:
      Plan Start = DATE (2018; 12; 08)
      Finish Plan = DATE (2019; = 1; 04)

    I called your table Schedule. I have assumed that if the first if condition is met, what you want to calculate is the number of days between the two dates and I used the DATEDIFF function.

    In the Schedule table, create the calculated column:

    DUE =
    IF (
         AND (
             MAX (Schedule [BL1 Strat], Schedule [BL1 Finish]) <= [Plan Finish],
             MIN (Schedule [BL1 Strat], Schedule [BL1 Finish])> = [Plan Start]
         ),
         DATEDIFF (Schedule [BL1 Strat], Schedule [BL1 Finish], DAY),
         IF (
             AND (
                 Schedule [BL1 Strat]> = [Plan Start],
                 Schedule [BL1 Strat] <= [Plan Finish]
             ),
             one,
             IF (
                 AND (
                     Schedule [BL1 Finish]> = [Plan Start],
                     Schedule [BL1 Finish] <= [Plan Finish]
                 ),
                 one,
                 0
             )
         )
    )

    I hope it helps you,
    Diana

    ------------------------------
    Diana Aguilera Reyna

    ------------------------------



  • 3.  RE: Excel Formula Translation to DAX

    Silver Contributor
    Posted Feb 11, 2019 09:56 AM
      |   view attached
    Hi @Diana Aguilera

    Thank you for your help on this. I have tried to apply your suggestion. However, its still throwing me a warning error (see below). As per your proposed solution from above DAX code ​here on this line
    DATEDIFF (Schedule [BL1 Strat], Schedule [BL1 Finish], DAY).

    It does not show the Schedule[BL1 Start] in the intellisense drop down list. Therefore what I am doing is inserting MAX function then it gives me the filed name to pick from. (I know I am doing something wrong here)

    I have created a disconnected table called Quick Measure  and placed all your suggested measures in that table. (Please use the measure named called DUE to correct this error

    I am also attaching a PBIX file for your kind perusal, so that I can get an overall picture of the model and what is expected.

    I look forward to your reply.... Many Thanks









    ------------------------------
    Zahid Shaikh
    9152245089
    ------------------------------

    Attachment(s)

    pbix
    LastPlanner Detailed.pbix   873K 1 version


  • 4.  RE: Excel Formula Translation to DAX

    Posted Feb 11, 2019 10:18 AM
      |   view attached
    Hi Zahid Shaikh,
    I have created a model with your data, check it and tell me if you need any change.
    Regards,


    ------------------------------
    Diana Aguilera

    ------------------------------

    Attachment(s)

    pbix
    EWR LP.pbix   156K 1 version


  • 5.  RE: Excel Formula Translation to DAX

    Silver Contributor
    Posted Feb 11, 2019 10:56 AM
    Hi Diana Aguilera

    Thank you for that, Yes I have check the result against my spreadsheet. However, the attached PBIX file from your end for the below row gives Due result = 14 (see the image below)

    However, if you filter on the Activity Name on the same record on excel file it give a total of 2 (see below image), I think some tweaking is required to get it all correct. Once much appreciated!!!!!


    ------------------------------
    Zahid Shaikh
    9152245089
    ------------------------------



  • 6.  RE: Excel Formula Translation to DAX

    Bronze Contributor
    Posted Feb 11, 2019 08:37 AM
    ​Hello Zahid,

    You will have many different options depending how you want to present results in Power BI. An easy straight forward way will be:
    -Create a starting measure
         Forecast Count = Count( 'tablename'[columnname] )
    -Then create a table, matrix, or chart and drop your [Forecast Count] measure into it. You may or may not need a date field as well (wait until you add the slicer next)
    -Create a Slicer and add your date field into it
         -when slicer is selected, the upper right has a little down arrow head that when clicked you can change the type of slicer it is.
         -chose the between option
    -Now set your dates to the Plan Start/Finish below

    This scenario will strictly return results for everything in this report based on those dates, unless changing how filters apply to each visual.
    But I'm assuming you may want different date ranges and results within that same table/matrix/chart.
    Then you may want to consider making you own custom Date table in Excel and import that into you Power BI model. Create a column indicating Periods 1, 2, 3 etc or however this breakdown is done.
    Then when you create a table/matrix/chart you can drop in that field and it will help separate those results for you already without even doing a date filter. Just dropping in the Forecast Count will present those totals per period.

    I hope this is helpful to lead you in the right direction.

    There are still other options but the whole report would need to be considered and understood before determining which direction you should go.

    ------------------------------
    Best Regards,
    Andrew Foulk
    ------------------------------



  • 7.  RE: Excel Formula Translation to DAX

    Silver Contributor
    Posted Feb 11, 2019 10:14 AM
      |   view attached
    Hi Andrew

    Many Thanks for your detailed suggestion. However, I have imported a table called periods which has some time range and that is connected to my main table in the model with many to 1 relationship. Please see the attached PBIX file

    Could you please help show the formula that gives the desired result as per column L in above excel file.

    NOTE: Use the measure named DUE in the table called Quick Measure to translate this formula to DAX​.

    I look forward to your kind reply.

    Regards
    Zahid

    ------------------------------
    Zahid Shaikh
    9152245089
    ------------------------------

    Attachment(s)

    pbix
    LastPlanner Detailed.pbix   873K 1 version


  • 8.  RE: Excel Formula Translation to DAX

    Bronze Contributor
    Posted Feb 12, 2019 09:28 AM
    Hi Zahid,
    I download your file but nothing shows up as a PBIX that I can open. Is there a special way to do this through sharing a file on the forums here? I never did that before.

    Are you attempting to recreate the exact layout and format like you excel file?
    Sometimes the best approach when adapting to Power BI is to re-think how you want to present your results.

    I also noticed you excel formula seemed to just be counting 2 cells if the dates were within your Start/Finish (so you will always get a 2), but then in Power BI you were doing a DATEDIFF count between the 2 dates. Am I reading both things correctly about that? If so is that would be why the PBIX had 14 days and your excel file had 2.

    ------------------------------
    Best Regards,
    Andrew Foulk
    ------------------------------



  • 9.  RE: Excel Formula Translation to DAX

    Silver Contributor
    Posted Feb 12, 2019 09:58 AM
    Hi Andrew

    I think thats where its giving me incorrect results using DATEDIFF instead I shoud be using COUNT (as it has been done in the excel file) and use measures instead of calculated columns to keep the file size to its minimum. ofcourse we then have to use iterators for eg: (MAXX or SUMX or MINX) to traverse through each individual row.

    I can download the PBIX file from your last thread (Please see the bottom part of the message from me) you should have a PBIX file attached I cant see you you cannot download it, see below image

    ------------------------------
    Zahid Shaikh
    9152245089
    ------------------------------



  • 10.  RE: Excel Formula Translation to DAX

    Silver Contributor
    Posted Feb 11, 2019 08:56 AM
    Hi Friends

    With my limited knowledge I have tried a conversion of above written Excel Formula to DAX. However, it is giving me a warning error saying

    (THE MIN FUNCTION ONLY ACCEPTS A COLUMN REFERENCE AS AN ARGUMENT)

    I am expecting the results as per column L in the attached excel file. Any help on this will be highly appreciated.
    Due with Measure = IF(AND(MAX(Sheet1[Finish] <= "2019-01-04" && MAX(Sheet1[BL1 Start]<= "2019-01-04",
    IF(AND(MIN(Sheet1[Finish] >= "2018-12-08" && MIN(Sheet1[BL1 Start])>= "2018-12-08"),COUNT(Sheet1[Finish])
    ),
    COUNT(Sheet1[BL1 Start]),
    IF(
        AND(MAX(Sheet1[Finish]>= "2018-12-08" && MAX(Sheet1[Finish]<= "2019-01-04"))
        ,1
        )
    ,IF(AND(MAX(Sheet1[BL1 Start]>= "2018-12-08"&& MAX(Sheet1[BL1 Start]<= "2019-01-04"
    ,1),0)​


    ------------------------------
    Zahid Shaikh
    9152245089
    ------------------------------