Excel formula to DAX Conversion

Anything related to PowerPivot and DAX Formuale
smjzahid
Posts: 7
Joined: Sat Sep 08, 2018 10:19 pm

Excel formula to DAX Conversion

Postby smjzahid » Mon Feb 11, 2019 1:33 am

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). I am also attaching sample Excel File for your perusal and the formula that needs conversion to DAX is written in Column L. (Just place your cursor on cell L9 to see the formula in the top formula bar within Excel File)

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

PlanStart = 08/12/2018

Plan Finish = 04/01/2019


Here is the formula written in Excel Workbook

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

=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
        )
    )
)


The result that is expected is should be as per column L in below image (highlighted in Red square)
[img]



[/img]

Thanks Everyone in Advance.
Attachments
LP.JPG
See the expected result highlighted in column L Please
LP.JPG (133.91 KiB) Viewed 708 times
EWR LP.xlsx
Sample Excel File (See the formula on column L Please
(112.55 KiB) Downloaded 25 times

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: Excel formula to DAX Conversion

Postby PhilC » Tue Feb 12, 2019 12:24 pm

Hi,

Suggest you bring the data into Power BI and provide a pbix file with the data already in a table ready for the DAX measure to be created. You could also do this in Excel / Power Pivot and transfer over to Power BI, but you indicated you are migrating to Power BI.

For PlanStart and PlanFinish, I'd add them to a column in the data for easy reference.

You have the choice of creating a DAX measure or a Calculated Column (ie another column in the table). Generally using a DAX measure is the better approach as it reduces file size. This may not be an issue in your model, and you may find a calculated column easier to understand as you develop knowledge of Power BI / DAX.

In terms of the DAX measure, you can write it in a very similar way, however you will need to use SUMX to ensure the calculation uses Row context and effectively does the calc one row at a time, then sums up the result.

Where you have AND clauses in your IF statement, use && to combine the components, rather than AND( , )

Cheers
Phil

smjzahid
Posts: 7
Joined: Sat Sep 08, 2018 10:19 pm

Re: Excel formula to DAX Conversion

Postby smjzahid » Wed Feb 13, 2019 12:41 am

Hi PhilC

Thank you for your explanation and suggestion, I would request you to please find the PBIX file attached the name of the calculation is DUE. I agree with you that writing a measure will keep the file size smaller as compared to calculated columns.

The attached file contains a calculated column with this formula. However, its not giving me the correct result for eg:
my excel file shows Due = 2 for the Activity ID 01Z_PWR_4_DES_3140. and the below formula writen in DAX shows 4

Can help convert this to a measure instead and rectify the problem please

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

DUE Calc = IF (
     AND (
         MAX (Sheet1[BL1 Start], Sheet1[BL1 Finish]) <= [Plan Finish],
         MIN (Sheet1[BL1 Start], Sheet1[BL1 Finish]) >= [Plan Start]
     ),
     DATEDIFF (Sheet1[BL1 Start], Sheet1[BL1 Finish], DAY),
      --  COUNT(Sheet1[BL1 Start]),COUNT(Sheet1[BL1 Finish])

     IF (
         AND (
             Sheet1[BL1 Start]>= [Plan Start],
             Sheet1[BL1 Start] <= [Plan Finish]
         ),
        1,
         IF (
             AND (
                 Sheet1[BL1 Finish] >= [Plan Start],
                 Sheet1[BL1 Finish] <= [Plan Finish]
             ),
             1,
             0
         )
     )
)


Could you please help me with this.

The sample Excel file is attached in the above thread

Many Thanks
Zahid
Attachments
EWR LP.pbix
(156.62 KiB) Downloaded 22 times

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: Excel formula to DAX Conversion

Postby PhilC » Wed Feb 13, 2019 10:30 am

Hi, I cannot find Activity ID 01Z_PWR_4_DES_3140 in either the PIBX or the Excel file. Do you have another example where the formula is not working?

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: Excel formula to DAX Conversion

Postby PhilC » Wed Feb 13, 2019 11:18 am

Hi, did some extra digging.

Looking at your DUE calculations in PBI, compared to the Excel formula, for the first part of the IF statement, in Excle you count the existence of a value, in PBI, you are doing a Date Difference, so this will result in different numbers.

Have added a calculated column called DUE Edited. Am sure there is a better way for counting the values in the Start and Finish column, but this seems to be working.

Matched up to the Excel file, only one difference where the data in Power BI has no dates but the Excel file does, so not sure what is happening there.

In terms of learning, you can look to convert to a measure rather than a calculated column, will need to use SUMX for that.

Cheers
Phil
Attachments
EWR LP-2 PPM.xlsx
(166.04 KiB) Downloaded 21 times
EWR LP PPM.pbix
(160.92 KiB) Downloaded 21 times


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 3 guests