I have the following formula which perfectly returns the sum of the roles row. However, I don't understand how it works because my roles column is not numeric values:
Sum for roles= CALCULATE(SUM(Table1[Value]),FILTER(Table1, Table1[Role]=max(Table1[Role])))
following is an screenshot of the dataset:
Solved! Go to Solution.
Hi @bestmbaman ,
In DAX the MAX function returns the largest value in a column, or the larger value between two scalar expressions. Ignores logical values. Strings are compared according to alphabetical order.
This is because of the way the internal engine of DAX works, so when comparing strings values it works correctly.
Check this documentation below:
I know what MAX function is doing for numerical values @MFelix , but if you take a closer look at my dataset, they are all TEXT (the Roles column) and not numbers! So, what is MAX function is doing here? Because when I use it in my table visual, it simply returns the last value in the last column before total column which is the result of the measure Sum for roles. Please check out the Screen shot below:
In the above table I only need the last row Total, but it also automatically visualizes the Total at the last column!
What should I do to get rid of this Total column @MFelix ?
@MFelixIf I don't pick the Total Measure in my visual then how I can show the the sum of Mother1,Mother2,etc. for each column!? The primary reason of calculating Total Measure for me was to show SUM of my columns but it also does the same for the rows ! which is reduntant and mislealeading
What should I do to have only SUM of each column?