Calculated Column/Measure DAX

[Anonymous]

Hi there,

Im wondering if anybody can please help me.

I have inherited this spreadsheet and appreciate its not best practice with this large a statement. In row AD of the spreadsheet it contains a large IF statement that runs off of the parameters on the first tab.

I'm trying to replicate this in Power BI to mirror the results, and then look to change it to make it cleaner. My problem is that the spreadsheet formula works out row by row and then it automatically totals for a pivot table. Half of my DAX query runs row by row, but then to contain the paramaters like Ive done in the variables, this would need to be in a measure. My aim is to have the two paramaters as just floating dates that the user can use the date slicer for.

Ive attached a spreadsheet that contains the full IF statement in Excel. It runs off 'Parameter Start Date' & 'Parameter End Date' which are stored in the 'Front Cover' tab in excel. The statement is in row AD of the booking spreadsheet and my calculated column is currently called 'Final Calc'. 

My aim is to have a pivot table / matrix with buildings (which ive took out for the sample), totalling hours booked for rooms, buildings etc. I realise it might not be possible to do as on the spreadsheet there was 2 parameters to go off, whereas I dont want any in Power BI.

https://1drv.ms/u/s!AtcnGX-0tS5riSX0H3wOW1DzRcdi?e=DMMPLn

Appreciate this is quite awkward, but If anybody could help or give any advice as to how best to replicate I would really appreciate it

Thanks in advance

Liam

[dedelman_clng]

Hi @Anonymous  - I see this issue has been out there for a while. If you are still looking for assistance, here are some things to consider

1) Nothing wrong with making the who calculation a measure.  Measures can be calculated at the row level. You just have to watch out if they are non-additive measures (where for example the value for a month is not the sum of the value for all of the days in the month).

2) SWITCH() would work better in your case than all of those nested IFs

SWITCH( TRUE(), //TRUE() here is just a placeholder
Condition1, Calculation1,
Condition2, Calculation2,
...,
ConditionN, CalculationN,
ELSE)

3) It looks like you're handling the parameters correctly.  Also instead of min/max you could consider having two fields from two different tables and you can use SELECTEDVALUE() for each.

It should also be noted that this PBIX appears to be optimized for Power BI Report Server (at least that was the message I got when I opened it up), and there are a couple of functions that don't exist on regular PBI Desktop, so it would be difficult for me to put the entire code together.

Let me know if any of this helps you.

David