Forum Discussion

kranthi82's avatar
kranthi82
Helper 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
Helper I
7 years ago

Anonymous Thank you so much for your advice. I indeed learn everyday DAX and try to solve myself in the first place. 

 

Here is some sample data and I hope you can help me.

 

 

The Log column data has been calculated from Excel so the DAX or Column expression should give the data as in the Log column and if I plot it then it should look as follows.

 

dax's avatar
dax
Community Support
7 years ago

Hi kranthi82,

Below is my data sample 

id         date                        amount    log

12019/8/1 10:50:54100:00:00
12019/8/1 10:51:25200:00:30
12019/8/1 10:51:54300:01:00
12019/8/1 10:52:25400:01:30
12019/8/1 10:52:54400:02:00
12019/8/1 10:53:25400:02:30
12019/8/1 10:53:54400:03:00
12019/8/1 10:54:25300:03:30
12019/8/1 10:54:54200:04:00
12019/8/1 10:55:25100:04:30
22019/8/1 11:25:20100:00:00
22019/8/1 11:25:50200:00:30
22019/8/1 11:26:20300:01:00
22019/8/1 11:26:50400:01:30
22019/8/1 11:27:20400:02:00
22019/8/1 11:27:50400:02:30
22019/8/1 11:28:20400:03:00
22019/8/1 11:28:50300:03:30
22019/8/1 11:29:20200:04:00
22019/8/1 11:29:50100:04:30
32019/8/1 12:23:33100:00:00
32019/8/1 12:24:03200:00:30
32019/8/1 12:24:33300:01:00
32019/8/1 12:25:03400:01:30
32019/8/1 12:25:33400:02:00
32019/8/1 12:26:03400:02:30
32019/8/1 12:26:33400:03:00
32019/8/1 12:27:03300:03:30
32019/8/1 12:27:33200:04:00
32019/8/1 12:28:03100:04:30


If you want to get result like below

 You could try to create measure like below

Measure 6 = AVERAGE('Table (2)'[amount])

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • kranthi82's avatar
    kranthi82
    Helper 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
      Super 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
        Helper I
        7 years ago

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

         

        /Kranthi