cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Parameter Logic

Hi there,

 

Could somebody please help me with some logic?

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?

 

Parameter.jpg

 

Thanks

 

Liam

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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





View solution in original post

19 REPLIES 19
Highlighted
Super User III
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
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

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

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

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]<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]?

 

Thankyou so much for your help

 

Highlighted

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

Hi @MFelix ,

 

Okay many thanks. Its much appreciated

Highlighted

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

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





Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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