Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I need your help to make a calculation based on previous rows and specific filters .
cars | ||
S | A | 0 |
S | A | 1 |
S | A | 0 |
S | A | 2 |
S | A | 1 |
S | A | 2 |
S | B | 0 |
S | B | 0 |
S | B | 1 |
Z | C | 0 |
Z | C | 2 |
How many cars for A/B/C ? i need formulas using language M in order to use it in data view to return new numerical formulas to be used on future columns.
Solved! Go to Solution.
Hi @zoubaier,
In this scenario, I would suggest you to use SUMMARIZE Function (DAX) to create a new calculate table in Data view. See my sample below.
I assume you have a table called "Table1" like below.
The you should be able to use the formula below to create a new table you need in Data view.
NewSummarizeTable = SUMMARIZE ( Table1, Table1[column2], "TotalCars", SUM ( Table1[cars] ) )
Regards
Hi @zoubaier,
In this scenario, I would suggest you to use SUMMARIZE Function (DAX) to create a new calculate table in Data view. See my sample below.
I assume you have a table called "Table1" like below.
The you should be able to use the formula below to create a new table you need in Data view.
NewSummarizeTable = SUMMARIZE ( Table1, Table1[column2], "TotalCars", SUM ( Table1[cars] ) )
Regards
Thanks @v-ljerr-msft for answering,
I don't prefer this option as i ll need to use the resulted sum in other calculation via query editor this why I prefer magic formulas on that .
I ll try working with it until finding another option.
Best Regards
On the Transform-tab top left you'll find the "Group By" button. Click that after you've checked a cell in the column you want to group by/aggregate.
You'll see a couple of options to create the aggregates, a SUM on the Cars-column will probably be what you want.
Just play around with it a bit and study the resulting codes.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hello @ImkeF,
Thanks for answering and trying to help.
The best solution i found is to to summary table (DAX Language) and do the following calculation based on it.
The group by option will modify the table source structure howerver i need it to keep the same structure to do other visualisations. This is one of the limit of Language M, hope in the future to find an option to do a group calculation or summary by in the query without changing the table structure.
Best Regards
Hello @zoubaier,
this option exists already. In mulitple ways actually:
1) If you have a table that you want to load as it is to the data model and want to create another table that is based on that, you just reference your first "big" query instead of further processing it (check the name of the query -> rightclick your mouse -> reference). This will create a new query that you can shape as you like.
2) If you ended up in a query that you have processed too far already, like in this case "destroyed all details by grouping", you can split this query into 2. Therefore you check the step that shall be the first in your new query -> rightclick your mouse -> "Extract Previous". This will open up a dialgog where you will be promted to type in a name for the "new" 1st query.
You'll find a nice presentation of this functionality here: https://www.youtube.com/watch?time_continue=796&v=2v7LUD7MJaw (start at 13:00)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries