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.
Hello,
I am trying to create a DAX measure with following table.
Column A | Column B | Column C | Date | Value |
1 | 1 | P1 | 10/26/2017 | 10 |
1 | 1 | P1 | 10/27/2017 | 20 |
1 | 3 | P3 | 10/28/2017 | 10 |
2 | 2 | P1 | 10/29/2017 | 15 |
2 | 2 | P2 | 10/30/2017 | 25 |
I want to dislpay the following grid data.
Column A | Column B | Value |
1 | 1 | 20 |
1 | 3 | 10 |
2 | 2 | 40 |
The measure is calculated as follows: It is a 2 step aggregation
Get the max value for each of the first 3 column values. i.e after the 1st aggregation the result would look like:
Column A | Column B | Column C | Value |
1 | 1 | P1 | 20 |
1 | 3 | P3 | 10 |
2 | 2 | P1 | 15 |
2 | 2 | P2 | 25 |
Now sum it over first 2 columns to get the final result which is:
Column A | Column B | Value |
1 | 1 | 20 |
1 | 3 | 10 |
2 | 2 | 40 |
Help me in creating the DAX measure which would result in the above data when I create a table visual in Power BI. Thanks in advance..
Solved! Go to Solution.
You can try
sum = VAR first_agg_tbl = SUMMARIZE ( yourTable, yourTable[Column C], "Max of each column C", MAX ( yourTable[Value] ) ) RETURN SUMX ( first_agg_tbl, [Max of each column C] )
You can try
sum = VAR first_agg_tbl = SUMMARIZE ( yourTable, yourTable[Column C], "Max of each column C", MAX ( yourTable[Value] ) ) RETURN SUMX ( first_agg_tbl, [Max of each column C] )
Thank you Eric. It was working great..
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 |
---|---|
13 | |
2 | |
2 | |
1 | |
1 |
User | Count |
---|---|
20 | |
3 | |
2 | |
2 | |
2 |