Forum Discussion

JoRo50's avatar
JoRo50
Frequent Visitor
4 years ago
Solved

Creating Dax code to dynamically calculate a "Not Group" value based on multiple slicers

Special shout out to AUaero  for helping out with the foundation for this problem (https://community.powerbi.com/t5/Desktop/calculation-to-show-value-for-NOT-variable/m-p/2665613) by showing me how t...
Help with DAX
Help with DAX Measure
  • AUaero's avatar
    AUaero
    4 years ago

    Thanks for the shoutout, JoRo50!  I loaded your sample data and was able to get the results you specified in your examples with these measures:

    % Completion = 
VAR SumEnrolled = SUM('SampleData'[Enrolled])
VAR SumTaskComplete = SUM('SampleData'[TaskComplete])

RETURN
DIVIDE(SumTaskComplete, SumEnrolled)
    % Complete - Non-Group Rate = 
VAR ThisInstructorStatus = MAX(SampleData[InstructorStatus])

RETURN 
CALCULATE(
    [% Completion],
    FILTER(
        ALLEXCEPT(SampleData, SampleData[Division], SampleData[StudentGender]),
        NOT(SampleData[InstructorStatus] = ThisInstructorStatus)
    )
)

    Does this get you what you are looking for?

AUaero's avatar
AUaero
Icon for Responsive Resident rankResponsive Resident
4 years ago

Thanks for the shoutout, JoRo50!  I loaded your sample data and was able to get the results you specified in your examples with these measures:

% Completion = 
VAR SumEnrolled = SUM('SampleData'[Enrolled])
VAR SumTaskComplete = SUM('SampleData'[TaskComplete])

RETURN
DIVIDE(SumTaskComplete, SumEnrolled)
% Complete - Non-Group Rate = 
VAR ThisInstructorStatus = MAX(SampleData[InstructorStatus])

RETURN 
CALCULATE(
    [% Completion],
    FILTER(
        ALLEXCEPT(SampleData, SampleData[Division], SampleData[StudentGender]),
        NOT(SampleData[InstructorStatus] = ThisInstructorStatus)
    )
)

Does this get you what you are looking for?