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

Attachments
LP.JPG
See the expected result highlighted in column L Please
LP.JPG (133.91 KiB) Viewed 1024 times
EWR LP.xlsx
Sample Excel File (See the formula on column L Please
(112.55 KiB) Downloaded 79 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

Attachments
EWR LP.pbix
(156.62 KiB) Downloaded 41 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 34 times
EWR LP PPM.pbix
(160.92 KiB) Downloaded 32 times


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest

cron