Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 🙂.
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 🙂.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |