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  instead of date on the x-axis. Can anyone help me write this Power BI? Thanks in advance.

 

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

     

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    DAX operates on the concept of columns, so what was your array will be column. Now the trick is to figure out how to change/modify what is in that table. 

     

    If you post some sample data and your expected outcome I can help you out with this specific example, but I'd recommend taking a look at these links from microsoft on dax

    https://docs.microsoft.com/en-us/dax/data-analysis-expressions-dax-reference

    • 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.