Creating a Measure To Return Summarized Values Based on Report Parameters/Slicers

[Shawn3001]

Hello,

I'm working with a large data set of manufacturing asset downtime and I'm trying to filter the display of a report table based on the values of a couple of report-level parameters.  Picture a snippet of my data (unfortunately, I'm not actually working with breakfast cereals but thought it was an amusing method for anonymity):

ID	Department	Asset	Delay Start	Delay End	Cause	Description	Duration
2924	Department #1	Asset #1	11/1/2020 1:58:03 AM	11/1/2020 2:58:03 AM	Operational	FROSTED MINI WHEATS	1.00
3891	Department #1	Asset #1	11/1/2020 5:42:46 PM	11/1/2020 11:59:59 PM	Operational	CAP'N CRUNCH	6.29
4004	Department #1	Asset #1	11/10/2020 1:57:45 PM	11/10/2020 2:20:30 PM	Operational	RICE KRISPIES	0.38
4281	Department #1	Asset #2	11/10/2020 10:18:19 PM	11/10/2020 11:27:31 PM	Operational	LUCKY CHARMS	1.15
3987	Department #1	Asset #1	11/10/2020 10:22:17 PM	11/10/2020 11:29:26 PM	Operational	LUCKY CHARMS	1.12
3990	Department #1	Asset #2	11/10/2020 11:27:31 PM	11/10/2020 11:51:47 PM	Operational	CINNAMON TOAST CRUNCH	0.40
3997	Department #1	Asset #2	11/10/2020 2:45:38 PM	11/10/2020 3:01:57 PM	Operational	FRUIT LOOPS	0.27
4229	Department #1	Asset #1	11/10/2020 8:40:13 PM	11/10/2020 9:02:35 PM	Operational	RICE KRISPIES	0.37
4561	Department #1	Asset #1	11/11/2020 10:30:48 AM	11/11/2020 10:52:50 AM	Operational	APPLE JACKS	0.37
4528	Department #1	Asset #1	11/11/2020 10:30:48 AM	11/11/2020 11:01:12 AM	Operational	APPLE JACKS	0.51
4548	Department #1	Asset #1	11/11/2020 11:40:05 AM	11/11/2020 12:03:26 PM	Operational	APPLE JACKS	0.39
4582	Department #1	Asset #1	11/11/2020 11:40:05 AM	11/11/2020 12:11:13 PM	Operational	APPLE JACKS	0.52
2911	Department #1	Asset #1	11/11/2020 3:44:33 PM	11/11/2020 4:10:51 PM	Operational	RICE KRISPIES	0.44
2912	Department #1	Asset #1	11/11/2020 3:44:33 PM	11/11/2020 4:22:48 PM	Operational	RICE KRISPIES	0.64
4746	Department #1	Asset #2	11/11/2020 5:57:17 PM	11/11/2020 6:18:24 PM	Mechanical	TRIX	0.35
4865	Department #1	Asset #2	11/11/2020 8:34:41 PM	11/11/2020 9:01:20 PM	Operational	RAISIN BRAN	0.44
5311	Department #1	Asset #1	11/12/2020 3:33:43 PM	11/12/2020 4:06:03 PM	Operational	RAISIN BRAN	0.54
5392	Department #1	Asset #1	11/12/2020 3:33:43 PM	11/12/2020 4:09:37 PM	Operational	RICE KRISPIES	0.60
5312	Department #1	Asset #2	11/12/2020 3:53:43 PM	11/12/2020 4:14:10 PM	Electrical	LIFE	0.34
5279	Department #1	Asset #1	11/12/2020 4:09:37 PM	11/12/2020 4:28:32 PM	Operational	RICE KRISPIES	0.32
5494	Department #1	Asset #2	11/12/2020 6:55:25 PM	11/12/2020 7:44:23 PM	Electrical	FRUITY PEBBLES	0.82
5411	Department #1	Asset #2	11/12/2020 8:24:35 AM	11/12/2020 8:56:04 AM	Operational	COCOA PEBBLES	0.52
5522	Department #1	Asset #2	11/12/2020 8:56:04 AM	11/12/2020 9:23:01 AM	Operational	CHEERIOS	0.45
5618	Department #1	Asset #1	11/13/2020 10:30:55 PM	11/13/2020 10:51:48 PM	Operational	FROSTED FLAKES	0.35
5751	Department #1	Asset #1	11/13/2020 11:05:38 PM	11/13/2020 11:23:44 PM	Operational	CINNAMON TOAST CRUNCH	0.30
5835	Department #1	Asset #1	11/13/2020 12:02:54 AM	11/13/2020 12:22:52 AM	Operational	APPLE JACKS	0.33
3264	Department #1	Asset #2	11/13/2020 3:06:33 AM	11/13/2020 3:22:29 AM	Operational	RAISIN BRAN	0.27
3262	Department #1	Asset #1	11/13/2020 4:08:19 PM	11/13/2020 4:39:46 PM	Mechanical	CORN FLAKES	0.52
3311	Department #1	Asset #1	11/13/2020 4:39:46 PM	11/13/2020 5:21:13 PM	Operational	CINNAMON TOAST CRUNCH	0.69

What I'm trying to do is create a measure(s) I can use to filter a table visual for records that meet criteria for frequency and duration (based on my report-level parameters).  For instance, if the user wants to only see records for [Description] that have appeared 5 times or more (per time period) with a [Duration] longer than 0.5, I'd like to filter a visual to only show these records:

ID	Department	Asset	Delay Start	Delay End	Cause	Description	Duration
4528	Department #1	Asset #1	11/11/2020 10:30:48 AM	11/11/2020 11:01:12 AM	Operational	APPLE JACKS	0.51
4582	Department #1	Asset #1	11/11/2020 11:40:05 AM	11/11/2020 12:11:13 PM	Operational	APPLE JACKS	0.52
2912	Department #1	Asset #1	11/11/2020 3:44:33 PM	11/11/2020 4:22:48 PM	Operational	RICE KRISPIES	0.64
5392	Department #1	Asset #1	11/12/2020 3:33:43 PM	11/12/2020 4:09:37 PM	Operational	RICE KRISPIES	0.60

Where I'm getting stuck is trying to preserve page-level filters for [Asset], [Department], [Delay Start], & [Cause] while still getting an accurate frequency count per time period dictated by the slicer for [Delay Start].  I tried an absolute monster (well at least for me) of a DAX statement but I could never consistently get the correct frequency count since [Description] values are not exclusive to the [Asset].  So if a specific [Description] code was used for multiple assets, it would count it as applicable even though, realistically, for the selected asset that [Description] may have only appeared once.

Severity = 
        IF (
            CONTAINS (
                SELECTCOLUMNS (
                    FILTER (
                        SUMMARIZE ( ALL('Data'), 
                            FILTER(
                                ALLEXCEPT(
                                    'Data',
                                    'Data'[Asset],
                                    'Data'[Department],
                                    'Data'[Cause]  
                                ), 
                            'Data'[Delay Start] >= EDATE(TODAY(), -12)  // This works but ideally would be based on user-input to slicer
                            ),
                            'Data'[Description],
                            "Record #s", COUNT ( 'Data'[ID] )
                        ),
                        [Record #s] > 'Very High'[Very High Value]  // This is my frequency parameter, adjustable by user
                    ),
                    "Delay_Desc", [Description]
                ),
                [Delay_Desc], SELECTEDVALUE ( 'Data'[Description] )
            ),
            "Catastrophic",
            "-"
        )

In response to this not working, I also tried:

• Creating a separate Table using SUMMARIZE for [Department], [Asset], [Description] and the Count of [ID] but I couldn't figure out how to get this to preserve my slicer settings and the count for each [Asset] was not correct.
• Creating a calculated column containing the summarized count of [ID] for each row entry and it's corresponding [Description] but couldn't get this to give the correct count either.

I'm sure I've made this more complicated than necessary in my troubleshooting.  Can someone help, please?

[Shawn3001]

Sorry, I know this is not super straight forward and I'm probably a little too close to the subject to explain it properly.

Think about the [Description] column as the failure-mode for an asset.  I'm not so concerned with low-frequency failure-modes (regardless of duration) because these are not the most detrimental to the asset and I'm not going to prioritize corrective actions to failure-modes that may only occur once or twice per year.  I'd rather focus on repetitive, high-frequency failure-modes and events that also have a moderate downtime duration associated with them.  This is why I'm looking to return a table of records containing [Description] values that occur frequently within my dataset and with high duration (according to those what-if parameters).  So ultimately, I need to look individually at Downtime Duration and holistically at Downtime Description.

Does that a do a better job of explaining the purpose?

[Shawn3001]

Ok that makes more sense.

Now how would you create a table visual to return each individual Record ID (and associated details) that would qualify as Severe? When I use the DISTINCTCOUNT function within the measure/table, it shows a 1 for every row entry so I can't filter to only show the most common Descriptions.

[littlemojopuppy]

Your definition of severe is "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" which implies groups of individual delays determine severity.  Individual delays will never be severe because they're individuals.

Unless you mean that an individual delay by itself would be severe if it exceeded a certain duration.  In that case, in the severity measure I provided remove the AND function and the condition of DelayCount > 'Delay Count'[Delay Count Value].

[littlemojopuppy]

Here's the code for all the measures I created...

Delay Count = DISTINCTCOUNT(Delays[ID])

Total Delay (Hours) = 
    SUMX(
		Delays,
		DATEDIFF(
			[Delay Start],
			[Delay End],
			HOUR
		)
	)

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"
)

[Delay Count] is just the distinct count of Delay ID.  [Delay Count Value] and [Delay Duration Value] are the what if parameters that the user could set.  You could drop Description into the rows for a matrix and [Delay Count] and {Total Delay (Hours)] in as values and they would calculate based on the filter context.

[Shawn3001]

Thanks for the response! However, I don't think this is quite what I'm looking for.

I'm looking for the count of [ID] by [Description], not necessarily by [Asset].  I need to preserve slicer selection for an Asset but what I really am looking for is how often each specific [Description] code is used.  This is where I tried to use the SUMMARIZE function to create a table like so:

Then, theoretically, I could filter this sumamrized table to only values that are greater than my What-If parameter, then use the CONTAINS (or something similar) to evaluate row-by-row whether an entry would be categorized as "Severe" (see below).  I could never seem to get the correct return from that DAX though.

[littlemojopuppy]

So I'm more than a little confused...you said in your original post "what I'm trying to do is create a measure(s) I can use to filter a table visual for records that meet criteria for frequency and duration (based on my report-level parameters)".  The severity measure I provided accomplishes exactly that.

Your "what I'm really looking for..." seems to be around presentation or perhaps you have a whole other idea for implementation?

[Shawn3001]

Sorry this is a little diffcult to explain without just being able to share my PBIX.

I was not able to download your PBIX so I wasn't able to see everything.  How do you calculate the number of occurences of a unique [Description]? The [Delay Count] in your Severity DAX is just a column of the what-if parameter values, correct?

[littlemojopuppy]

Can you explain in plain English what severity means without referring to your DAX measure?  This doesn't seem like it's very difficult but hard to figure out what you were trying to do with the Severity measure...

[Shawn3001]

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!

[littlemojopuppy]

Howdy!  This is kind of what you're aiming for?

Here's the link to download the PBIX.

[littlemojopuppy]

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.