The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to Solution.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsDAX 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
@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.
Hi kranthi82,
Below is my data sample
id date amount log
1 | 2019/8/1 10:50:54 | 1 | 00:00:00 |
1 | 2019/8/1 10:51:25 | 2 | 00:00:30 |
1 | 2019/8/1 10:51:54 | 3 | 00:01:00 |
1 | 2019/8/1 10:52:25 | 4 | 00:01:30 |
1 | 2019/8/1 10:52:54 | 4 | 00:02:00 |
1 | 2019/8/1 10:53:25 | 4 | 00:02:30 |
1 | 2019/8/1 10:53:54 | 4 | 00:03:00 |
1 | 2019/8/1 10:54:25 | 3 | 00:03:30 |
1 | 2019/8/1 10:54:54 | 2 | 00:04:00 |
1 | 2019/8/1 10:55:25 | 1 | 00:04:30 |
2 | 2019/8/1 11:25:20 | 1 | 00:00:00 |
2 | 2019/8/1 11:25:50 | 2 | 00:00:30 |
2 | 2019/8/1 11:26:20 | 3 | 00:01:00 |
2 | 2019/8/1 11:26:50 | 4 | 00:01:30 |
2 | 2019/8/1 11:27:20 | 4 | 00:02:00 |
2 | 2019/8/1 11:27:50 | 4 | 00:02:30 |
2 | 2019/8/1 11:28:20 | 4 | 00:03:00 |
2 | 2019/8/1 11:28:50 | 3 | 00:03:30 |
2 | 2019/8/1 11:29:20 | 2 | 00:04:00 |
2 | 2019/8/1 11:29:50 | 1 | 00:04:30 |
3 | 2019/8/1 12:23:33 | 1 | 00:00:00 |
3 | 2019/8/1 12:24:03 | 2 | 00:00:30 |
3 | 2019/8/1 12:24:33 | 3 | 00:01:00 |
3 | 2019/8/1 12:25:03 | 4 | 00:01:30 |
3 | 2019/8/1 12:25:33 | 4 | 00:02:00 |
3 | 2019/8/1 12:26:03 | 4 | 00:02:30 |
3 | 2019/8/1 12:26:33 | 4 | 00:03:00 |
3 | 2019/8/1 12:27:03 | 3 | 00:03:30 |
3 | 2019/8/1 12:27:33 | 2 | 00:04:00 |
3 | 2019/8/1 12:28:03 | 1 | 00: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.
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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix , Thanks a lot!! DAX seems to be the better solution, very close to the excel formula.
/Kranthi
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
81 | |
78 | |
44 | |
39 |
User | Count |
---|---|
150 | |
116 | |
68 | |
64 | |
57 |