DAX Measure to find Distinct Category where Error Rate has increased

[Vijay08V]

Hi ,

I need some help with DAX measure. I have sample table with different categories with Error rate for Quarter 1 and Quarter 2. I want to find distinct count of Categories where the Error rate has increased from last quarter to current quarter. 

Sample Data :

Year	QTR	Category	Error Rate
2023	1	A	20%
2023	1	B	10%
2023	1	C	15%
2023	1	D	5%
2023	1	E	10%
2023	2	A	10%
2023	2	B	20%
2023	2	C	30%
2023	2	D	10%
2023	2	E	5%

In above sample data Category B,C and D has error rate increased from Q1 to Q2. The expected output here would be 3.

[ThxAlot]

A simple use case of DAX Window function.

Err Incr.pbix

[rubayatyasmin · Accepted solution]

@Vijay08V hi,

here is the DAX,

Categories with Increased Error Rates =

CALCULATE(

    COUNTROWS(

        FILTER(

            'Table',

            'Table'[QTR] = 2 &&

            'Table'[Error Rate] > CALCULATE(MAX('Table'[Error Rate]), FILTER('Table', 'Table'[QTR] = 1 && 'Table'[Category] = EARLIER('Table'[Category])))

        )

    )

)

output: