Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi @Anonymous ,

 

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
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

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.
MFelix
Super User
Super User

Hi @Anonymous ,

 

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



Anonymous
Not applicable

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

Hi @Anonymous ,

 

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



Anonymous
Not applicable

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

 

Hi @Anonymous ,

 

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



Anonymous
Not applicable

Hi @MFelix ,

 

Okay many thanks. Its much appreciated

Hi @Anonymous ,

 

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



Hi @Anonymous ,

 

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



Anonymous
Not applicable

Hi @MFelix ,

 

Thankyou so much for that.  I dont get the July version util the 28th July so I will have to wait to open the file.

 

Is there another way to send it? If not I will wait 🙂

 

Thanks again

Hi @Anonymous ,

 

Since I have saved it on the July version there is no chance to open it on previous version, so I guess you have to wait.

 

but in the meantime can you check the excel file please?


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



Anonymous
Not applicable

Hi @MFelix ,

 

Ive just looked at the spreadsheet and thats all perfect.

 

Just now getting the decimals in Power BI and showing Hours and Minutes if thats possible, 

 

I can see hour datediffs in the dax. Would this mean it wouldnt show minutes?

 

Thanks again for all of your time with this, Its really appreciated.

 

Thanks

Hi @Anonymous ,

 

That can be showed in minutes, hours, seconds and so on. I just place the values to be equal tro the spreadsheet.


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



Anonymous
Not applicable

Hi @MFelix ,

 

Could I please ask how? I only ask because if I refer to the bottom row of the spreadsheet attached, your calculation gets 0.0903 which is correct and translates to 2:10 on number 18.

 

With the measure this equates to 3:00. Is this possible to equal 2:10 in power bi?

 

https://1drv.ms/x/s!AtcnGX-0tS5riTTd-oVC0ulg2pe1?e=qZQZ3f

 

Thanks 🙂

Hi @Anonymous ,

 

As refered there are some changes that need to be done, but basically there is the need to get the number in the correct value and then make the formatting to the correct value.

 

In the case you are refering we need to multiply the 0.0903 by 24 number of hours in one day. Also the calculation probably needs to be done in minutes and not in hours.

 

Also we need to take into account the order of the switch function.

 

Since you already told me that the values are matching I will try and carry on and tweak the calculations on the measure so that eveything is running smothly.


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



Anonymous
Not applicable

Okay thankyou @MFelix . Could I just ask one more question regarding that?

 

The 0.0903 * 24 = 2.1672. In Power BI it automatically takes it as 2.2. Is there a function or way to make it 2.10 as 2.2 is incorrect in time terms.

 

Thanks

 

Liam

Hi @Anonymous ,

 

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



Anonymous
Not applicable

@MFelix 

 

Ive managed to get a copy of the July version and view the PBIX. 

 

That is perfect! Its just a case of the forumula to convert accurately to hours and minutes

 

Thankypu

Anonymous
Not applicable

Okay thankyou 🙂 that would be great  @MFelix 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.