Forum Discussion
Helper IIDAX to Ignore Filters in Table Visual
Hello community, I'm trying to create a measure to calculate total per group that ignores some filters/columns in the table visualization. I've tried REMOVEFILTERS() and ALLEXCEPT(), but they are not returning what I'm looking for. In my student test example below, I want this measure to calcualte total test score per student, class and subject. Thank you in advance.
Model:
Table "tests":
Table "class_dim":
Table "student_dim":
Table "subject_dim":
Result - using REMOVEFILTERS(). Issue: Returning correct totals, but creating extra unwanted rows. Also, I need to modify REMOVEFILTERS() in the measure everytime I need to introduce new columns from "tests" table to the visual.
total score - remove filters =
CALCULATE(
SUM(tests[score]),
REMOVEFILTERS(tests[test]),
REMOVEFILTERS(tests[test_date])
)
Result - Using ALLEXCEPT(). Issue: Not summing correctly by group.
total score - all except =
CALCULATE(
SUM(tests[score]),
ALLEXCEPT(student_dim, student_dim[student]),
ALLEXCEPT(class_dim, class_dim[class]),
ALLEXCEPT(subject_dim, subject_dim[subject])
)
3 Replies
IrwanSuper User
hello DataSundowner
please check if this accomodate your need.
create a new measure for calculating SUM of score (i used your DAX with a little tweak)
Total Score =
CALCULATE(
SUM(tests[score]),
FILTER(
ALLEXCEPT('tests','student'[student],'subject'[subject],'class'[class]),
'tests'[test_date]<=MAX('tests'[test_date])
)
)i add that red line. this measure will calculate total score based on same student, subject, and class as you did above.however, as you can see above, the second line has total score of 78 instead of 167.this happens because of filter in table visual as you mentioned in your post above.
second line and third line are student a, class 1, and subject math but the test is different.
if you want to show only the maximum of sum value, then try creating another measure with following DAX.Total Score MAX =
var _Student = SELECTEDVALUE('tests'[student])
var _Subject = SELECTEDVALUE('tests'[subject])
var _Class = SELECTEDVALUE('tests'[class])
Return
MAXX(
FILTER(
ALL('tests'),
'tests'[student]=_Student&&
'tests'[subject]=_Subject&&
'tests'[class]=_Class
),
[Total Score]
)this DAX will take the maximum value of total score based on same student, same subject, and same class (ignoring the test different).
Hope this will help.Thank you.- techies
Please check this
Total Score =
CALCULATE(
SUM(tests[score]),
ALLEXCEPT(tests, tests[student], tests[class], tests[subject])
) - Ashish_Mathur
Hi,
Share the download link of the PBI file. Show the problem and expected result very clearly.
