Forum Discussion
Creating a Measure To Return Summarized Values Based on Report Parameters/Slicers
Yes of course.
A "Severe" delay would be one that exceeds a specific Duration value AND occurs greater than n number of times per (in this case) year. Given the example above, those 4 delays would be qualified as "Severe" or "Catastrophic" because each instance exceeds the Duration threshold of 0.5 hours, and those specific Description codes ("Rice Krispies" & "Apple Jacks") appear more than n times within the whole dataset.
Hopefully that makes more sense!
Sorry...errant click. Will continue...
The measure to calculate Severity is as follows...Severity = VAR DelayCount = [Delay Count] VAR DelayDuration = [Total Delay (Hours)] RETURN SWITCH( TRUE(), AND( DelayCount > 'Delay Count'[Delay Count Value], DelayDuration > 'Delay Duration'[Delay Duration Value] ), "Catastrophic", AND( DelayCount > 'Delay Count'[Delay Count Value], DelayDuration > 'Delay Duration'[Delay Duration Value] ), "Severe", "Normal" )For this to work correctly, you need to have two "what if" parameters for Delay Duration and Delay Count in your data model. You can find those under Modeling on the Ribbon. In short, those create the ability for your users to determine at what levels something is catastrophic or not.
One thing I just noticed is that the measure compares delay count/duration to the appropriate parameter for both "catastrophic" and "severe" otherwise it will return normal. The way I defined catastrophic and severe are identical so if it meets the definition, it will never return severe (because it always returns the first true condition). You should figure out how to want to modify the definitions to differentiate between the two. But otherwise I think this is giving you exactly what you described.