Yes, Dax formula not able to recognize and process with logical expressions that stored in string.
In my opinion, I'd like to suggest you take a look at power query functions Expression.Evaluate, it is able to recognize and processing logic expressions. (notice: you need to convert them into 'power query' expression format first)
In the spreadsheet tab of front cover is where the parameter is entered. The column AD then does the IF statement, which would be fine in a calculated column, but a large chunk of the logic runs off of parameters in the code, which can only be used in a measure.
Ive tried to replicate the spreadsheet in the same way
Any help or an alternative way to do this would be really appreciated
thank you for the files can you please explain a little bit better the calculations, you have nested if's and some calculations based on another table banking days, are you abble to simplify the reading of that code?
Sorry for the question but if I try and read it on my own I will take more time and believe is easier to ask you since you made the logic.
Did I answer your question? Mark my post as a solution!
I have inherited the spreadsheet and thats what I'm struggling with. I know its a very large nested statement and it isnt best practice, but If it just got the first couple of IF's to work on Power BI, I could do the rest.
It is converting that column (AD) to Power BI format so that I can move a date slicer instead of entering the two dates like on the front page tab on excel.
It might be easier if we could sort one IF statement as they are all quite similar?
If I use below as example from Excel.
IF(AND([New End Date]=ParameterEndDate,[Start Date]<ParameterStartDate
How could this translate to a measure? Would you put min or max infront of the field [New End Date] or [New Start Date]?
I was working on your file and made a few adjustments. Before explaining them I would like for you to analyze the result and see if you aggre with some of my conclusions:
I have split the IF formulas on the excel spreadsheet and realized that most of the columns give 0 so if on all the lines the values are 0 the IF statement - believe that you need to go one by one of the if statments and see if there isn't duplicated sentances or if they make sense
The last IF statment that refers to the ID 13299 is not giving the correct result but let's check it in the next steps.
I attach the excel file with the IF statements split and PBIX file (saved on Power BI desktop July version)
I have made the following changes to your data:
Parameter Start and End Date were refering to the incorrect date column
ParameterStartDate = MIN(Parameter[Date])
ParameterEndDate = MAX(Parameter[Date])
End Time and Start time were incorrectly calculated:
Start Time, Minimum 8am =
IF(RoomUtilisation[Start Day] >= 6;TIME(18;0;0);
IF(TIME(HOUR(RoomUtilisation[Start Time]);MINUTE(RoomUtilisation[Start Time]);SECOND(RoomUtilisation[Start Time]))>TIME(18;0;0);TIME(18;0;0);
IF(TIME(HOUR(RoomUtilisation[Start Time]);MINUTE(RoomUtilisation[Start Time]);SECOND(RoomUtilisation[Start Time])) < TIME(8;0;0); TIME(8;0;0);RoomUtilisation[Start Time])))
End Time, Maximum 6pm =
IF(TIME(HOUR(RoomUtilisation[End Time]);MINUTE(RoomUtilisation[End Time]);SECOND(RoomUtilisation[End Time])) > TIME(18;0;0); TIME(18;0;0);
IF(TIME(HOUR(RoomUtilisation[End Time]);MINUTE(RoomUtilisation[End Time]);SECOND(RoomUtilisation[End Time])) < TIME(8;0;0); TIME(8;0;0);RoomUtilisation[end time])))