Forum Discussion
Summary and Addcolumns
Hi,
i'm working on a somewhat komplikatet problem in PowerBI/DAX.
I have one Tablle how has a countetn like this:
This table was created by union from 4 individual tables.
| Date | Article number | Quantity | Quantity greater than 0 | Quantity greater than 1 | Quantity greater than 2 | Quantity greater than 3 | Year |
| 01.05.2019 | 1 | 1 | TRUE | FALSE | FALSE | FALSE | 2019 |
| 02.05.2019 | 2 | 4 | TRUE | TRUE | TRUE | TRUE | 2019 |
| 03.05.2019 | 3 | 5 | TRUE | TRUE | TRUE | TRUE | 2019 |
| 04.05.2019 | 1 | 6 | TRUE | TRUE | TRUE | TRUE | 2019 |
| 05.05.2019 | 2 | 7 | TRUE | TRUE | TRUE | TRUE | 2019 |
| 06.05.2019 | 3 | 8 | TRUE | TRUE | TRUE | TRUE | 2019 |
| 07.05.2019 | 1 | 2 | TRUE | TRUE | FALSE | FALSE | 2019 |
| 08.05.2019 | 2 | 2 | TRUE | TRUE | FALSE | FALSE | 2019 |
| 09.05.2019 | 3 | 3 | TRUE | TRUE | TRUE | FALSE | 2019 |
| 10.05.2019 | 1 | 6 | TRUE | TRUE | TRUE | TRUE | 2019 |
| 20.03.2020 | 2 | 9 | TRUE | TRUE | TRUE | TRUE | 2020 |
| 21.03.2020 | 3 | 2 | TRUE | TRUE | FALSE | FALSE | 2020 |
| 22.03.2020 | 1 | 1 | TRUE | FALSE | FALSE | FALSE | 2020 |
| 23.03.2020 | 2 | 3 | TRUE | TRUE | TRUE | FALSE | 2020 |
| 24.03.2020 | 3 | 4 | TRUE | TRUE | TRUE | TRUE | 2020 |
| 25.03.2020 | 1 | 5 | TRUE | TRUE | TRUE | TRUE | 2020 |
| 26.03.2020 | 2 | 9 | TRUE | TRUE | TRUE | TRUE | 2020 |
| 27.03.2020 | 3 | 4 | TRUE | TRUE | TRUE | TRUE | 2020 |
| 28.03.2020 | 1 | 3 | TRUE | TRUE | TRUE | FALSE | 2020 |
| 29.03.2020 | 2 | 2 | TRUE | TRUE | FALSE | FALSE | 2020 |
| 30.03.2020 | 3 | 8 | TRUE | TRUE | TRUE | TRUE | 2020 |
| 31.03.2020 | 1 | 9 | TRUE | TRUE | TRUE | TRUE | 2020 |
| 01.04.2020 | 2 | 1 | TRUE | FALSE | FALSE | FALSE | 2020 |
| 02.04.2020 | 3 | 0 | FALSE | FALSE | FALSE | FALSE | 2020 |
My goal is to create from this table with Summary and Addcolumns a summary by part number, years and number of TRUE in columns Quantity greater than X.
Therefore, my expectet result, should look like this:
| Article number | Jahr | Quantity greater than 0 | Quantity greater than 1 | Quantity greater than 2 | Quantity greater than 3 |
| 1 | 2019 | 4 | 3 | 2 | 2 |
| 2 | 2019 | 3 | 3 | 2 | 2 |
| 3 | 2019 | 3 | 3 | 3 | 2 |
| 1 | 2020 | 4 | 4 | 4 | 4 |
| 2 | 2020 | 5 | 5 | 5 | 5 |
| 3 | 2020 | 4 | 4 | 3 | 3 |
My procedure in DAX is the following, but unfortunately it does not work.
SummaryTable = SUMMARIZE(T1,T1[part number],T1[year], ADDCOLUMNS(T1, "quantity 1",CALCULATE(COUNTA(T1[quantity 1]),T1[quantity 1]="TRUE")))
Since I am not a DAX expert, I need help.
Here is a link to the sampel Excel file: https://1drv.ms/x/s!AoFqgLqZH-C0hMl1UsNeeeAwHaqpSw?e=vkyBtw
Thanks a lot
What I would do is join the tables by means of Query with the Option Combine, to optimize resources.
While I understood what you are looking for is to group if the quantity x exceeds the desired value.
What I did was simulate your situation in my scenario and I had to create a table in query (combine) to add a conditional column that meets the criterion of what you mention, however place conditional result 1 so that these can be added when using the option group by-
conditional result added, to be able to perform summary.
the next thing I did was use the group/advanced option and place the conditionals and I got as a result what you are looking for.
However if you want to do it in dax, you should have a good xq team run very extensive measures consuming many resources.
I hope I have been able to help you more than anything 🙂.
1 Reply
What I would do is join the tables by means of Query with the Option Combine, to optimize resources.
While I understood what you are looking for is to group if the quantity x exceeds the desired value.
What I did was simulate your situation in my scenario and I had to create a table in query (combine) to add a conditional column that meets the criterion of what you mention, however place conditional result 1 so that these can be added when using the option group by-
conditional result added, to be able to perform summary.
the next thing I did was use the group/advanced option and place the conditionals and I got as a result what you are looking for.
However if you want to do it in dax, you should have a good xq team run very extensive measures consuming many resources.
I hope I have been able to help you more than anything 🙂.