Summary and Addcolumns

[Yonah]

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

[Syndicate_Admin · Accepted solution]

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