## 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

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]

Attachments
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

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

### Re: Excel formula to DAX Conversion

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

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

The sample Excel file is attached in the above thread

Many Thanks
Zahid
Attachments
EWR LP.pbix

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

### Re: Excel formula to DAX Conversion

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

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
EWR LP PPM.pbix

### Who is online

Users browsing this forum: No registered users and 3 guests