cancel
Showing results for
Did you mean:
Highlighted
Post Prodigy

## Parameter Logic

Hi there,

Below are the IF statements I need for Power BI from Excel, but New End Date and New Start Date are column fields, and with  parameters only for measures, I can't think of a way to work this out.

A measure would total dates which wouldnt make sense, but a parameter can't be used in a calculated column. The parameter must be inputted by the user

Is there any way around this?

Thanks

Liam

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III

Hi @lherbert502 ,

This need to be formatted so the calculation will be something similar to this:

0.0903 * 24  = 2.1673

(2.1673 - 2 ) * 60 = 10

2: 10

Basically is making the calculation in hours and picking only the integer, then multiply the decimals by 60 minutes and so on.

I have the formula and can give you but need to check it on a previous file since I don't know by heart.

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português

19 REPLIES 19
Highlighted
Super User III

Hi @lherbert502 ,

Is this calculation for a measure or for a additional column on your data?

If this is for a measure you can create a Calendar table to use as a slicer and then make the reading of that date to use on your measure would be something similar to this:

``Measure = SWITCH(TRUE(), MAX(Table[New End Date]) = SELECTEDVALUE (Calendar[Date]) , 0 , 1)``

Can you provide a sample data and expected results, only having the IF statement is difficult to provide the best approach.

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português

Highlighted
Community Support

Hi @lherbert502,

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)

Expression.Evaluate() In Power Query/M

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Post Prodigy

Hi @MFelix ,

Ive attached the onedrive link for the sample PBIX and original spreadsheet.

https://1drv.ms/u/s!AtcnGX-0tS5riSwqijtTCRyHvMZ_?e=4VTB5x

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

Thanks

Liam

Highlighted
Super User III

Hi @lherbert502 ,

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.

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português

Highlighted
Post Prodigy

Hi @MFelix ,

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]

How could this translate to a measure? Would you put min or max infront of the field [New End Date] or [New Start Date]?

Thankyou so much for your help

Highlighted
Super User III

Hi @lherbert502 ,

Thank you for the information I will try and check the data, and will get back to you later, probably not today but will try to do it ASAP.

I could give you some pointers but looking at some things you have in the file there is a coulple of calculations that aren't direct so I will need to dig in further.

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português

Highlighted
Post Prodigy

Hi @MFelix ,

Okay many thanks. Its much appreciated

Highlighted
Super User III

Hi @lherbert502 ,

I have not forgotten about this post, will try to get back ASAP, sorry for the delay.

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português

Highlighted
Super User III

Hi @lherbert502 ,

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
``````New syntax
ParameterStartDate = MIN(Parameter[Date])
ParameterEndDate = MAX(Parameter[Date])
``````
• End Time and Start time were incorrectly calculated:
``````New Syntax

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(RoomUtilisation[End Day]>=6;TIME(18;0;0);
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])))
``````

• Measure used for the calculations:
``````Measure =
VAR bankHolidays =
CALCULATE (
COUNT ( BankHols[Date] );
FILTER ( ALL ( BankHols[Date] ); BankHols[Date] > [ParameterStartDate] )
)
VAR NetworkDays =
COUNTROWS ( FILTER ( Parameter; NOT ( [DayofWeek] IN { 1; 7 } ) ) )
VAR CoreHourStar =
TIME ( 8; 0; 0 )
RETURN
SUMX (
RoomUtilisation;
IF (
SELECTEDVALUE ( RoomUtilisation[Status] ) <> "Approved";
0;
SWITCH (
TRUE ();
RoomUtilisation[New Start Date] > Parameter[ParameterStartDate]
&& RoomUtilisation[New End Date] < Parameter[ParameterEndDate]
&& RoomUtilisation[New Start Date] = RoomUtilisation[New End Date]; DATEDIFF (
RoomUtilisation[Start Time, Minimum 8am];
RoomUtilisation[End Time, Maximum 6pm];
HOUR
);
--18
RoomUtilisation[New Start Date] > Parameter[ParameterStartDate]
&& RoomUtilisation[New End Date] < Parameter[ParameterEndDate]; ( NetworkDays - bankHolidays - 2 ) * 10 / 24
+ DATEDIFF ( CoreHourStar; RoomUtilisation[Start Time, Minimum 8am]; HOUR )
+ DATEDIFF ( RoomUtilisation[End Time, Maximum 6pm]; CoreHourStar; HOUR );
--19
RoomUtilisation[New Start Date] = RoomUtilisation[New End Date]
&& bankHolidays > 0; 0;
--1
RoomUtilisation[New Start Date] = Parameter[ParameterStartDate]
&& RoomUtilisation[New End Date] > Parameter[ParameterStartDate]
&& bankHolidays > 0; ( NetworkDays - bankHolidays ) * 10 / 24
+ DATEDIFF ( RoomUtilisation[End Time, Maximum 6pm]; CoreHourStar; HOUR );
--2
RoomUtilisation[New End Date] = Parameter[ParameterStartDate]
&& bankHolidays > 0; 0;
--3
RoomUtilisation[New End Date] = Parameter[ParameterEndDate]
&& bankHolidays > 0; 0;
--4
RoomUtilisation[New End Date] = Parameter[ParameterStartDate]
&& RoomUtilisation[New Start Date] < Parameter[ParameterStartDate]
&& bankHolidays > 0; ( NetworkDays - bankHolidays ) * 10 / 24;
--5
RoomUtilisation[New End Date] = Parameter[ParameterEndDate]
&& RoomUtilisation[New Start Date] > Parameter[ParameterStartDate]
&& bankHolidays > 0; ( NetworkDays - bankHolidays ) * 10 / 24
+ DATEDIFF ( CoreHourStar; RoomUtilisation[Start Time, Minimum 8am]; HOUR );
--6
RoomUtilisation[New Start Date] > Parameter[ParameterStartDate]
&& RoomUtilisation[New End Date] < Parameter[ParameterEndDate]
&& bankHolidays > 0; ( NetworkDays - bankHolidays ) * 10 / 24
+ DATEDIFF ( CoreHourStar; RoomUtilisation[Start Time, Minimum 8am]; HOUR );
--7
RoomUtilisation[New Start Date] < Parameter[ParameterStartDate]
&& RoomUtilisation[New End Date] > Parameter[ParameterStartDate]
&& RoomUtilisation[New End Date] < Parameter[ParameterEndDate]
&& bankHolidays > 0; ( NetworkDays - bankHolidays ) * 10 / 24;
--8
RoomUtilisation[New Start Date] < Parameter[ParameterStartDate]
&& RoomUtilisation[New End Date] > Parameter[ParameterStartDate]
&& bankHolidays > 0; ( NetworkDays - bankHolidays ) * 10 / 24
)
)
)``````

This still need to be formatted to hours.

Can you please look at the information and get back to me.

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors