Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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?

 

 

Thanks

 

Liam

Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    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.

19 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

      • MFelix's avatar
        MFelix
        Super User
        6 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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