Forum Discussion

kranthi82's avatar
kranthi82
Icon for Helper I rankHelper I
7 years ago
Solved

Array formula

Hello, By using array formula ( {=B2-MIN(IF($A$2=$A$2:$A$51;$B$2:$B$51))}, where column A is ID and column B is date/time ), in excel I could plot values under a period of time, say 0 to 6 min  inst...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi kranthi82 ,

     

    Try to use the following calculated column:

     

    Log_Dax_Based =
'Table'[LogTime]
    - CALCULATE ( MIN ( 'Table'[LogTime] ); ALLEXCEPT ( 'Table'; 'Table'[ID] ) )

    You can also do it based on a query editor, check the PBIX file with both options.

     

    I also got a Logs Excel column so you can compared it with the results.

     

    Regards,

    MFelix

     

kranthi82's avatar
kranthi82
Icon for Helper I rankHelper I
7 years ago

Hi dax , 

Thank you so much for your effort but I think I need to reframe the question. My original data doesn't contain column Log. I would like to write a DAX formuala so that I can get column Log. In excel to get that column I use an array formula and I don't know how to do it with DAX. I hope you understand my problem now.

MFelix's avatar
MFelix
Icon for Super User rankSuper User
7 years ago

Hi kranthi82 ,

 

Try to use the following calculated column:

 

Log_Dax_Based =
'Table'[LogTime]
    - CALCULATE ( MIN ( 'Table'[LogTime] ); ALLEXCEPT ( 'Table'; 'Table'[ID] ) )

You can also do it based on a query editor, check the PBIX file with both options.

 

I also got a Logs Excel column so you can compared it with the results.

 

Regards,

MFelix

 

  • kranthi82's avatar
    kranthi82
    Icon for Helper I rankHelper I
    7 years ago

    MFelix , Thanks a lot!! DAX seems to be the better solution, very close to the excel formula.

     

    /Kranthi

  • kranthi82's avatar
    kranthi82
    Icon for Helper I rankHelper I
    7 years ago

    Hi again MFelix ,

    Thanks a lot once again for the solution.

     

    I came across another problem while plotting where I don't wanna use summarize. How can I do that. You plotted values with average and if I use SUM then it turns out to be like this.

    I'd like to just plot log to the values by using any of the visuals. 

    Thank you in advance,

     

    /Kranthi.

     

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      7 years ago

      Hi kranthi82 ,

       

      Not really sure of what you are refering to using AVERAGE instead of SUM and without summarize, can you explain a little bit better.

       

      Regards,

      MFelix