distinctcount item filtered by value

Thank you bhanu_gautam, I have the following thoughts:

Hi, Mike91 

Based on your chat with Super User, I used the example data you provided as shown in the image below:

First, I create a calculated table using this DAX expression:

Table 2 = 
VAR _table = SUMMARIZE(
	'Table',
	'Table'[Date],
	'Table'[Item],
	'Table'[Value],
	"Month", FORMAT(
		'Table'[Date],
		"MMMM"
	),
	"Year", FORMAT(
		'Table'[Date],
		"YYYY"
	)
)
RETURN
ADDCOLUMNS(
	ADDCOLUMNS(
		SUMMARIZE(
			_table,
			'Table'[Item],
			[Month],
			[Year]
		),
		"Res", VAR _year = [Year]
		VAR _month = [Month]
		VAR _item = 'Table'[Item]
		RETURN
			SUMX(
				FILTER(
					ALL('Table'),
					FORMAT(
						'Table'[Date],
						"YYYY"
					) = _year && FORMAT(
						'Table'[Date],
						"MMMM"
					) = _month && 'Table'[Item] = _item
				),
				'Table'[Value]
			)
	),
	"IsAbove100", IF(
		[Res] > 100,
		1,
		0
	)
)

This will calculate the total values for each item for each month and determine if the total values for that month are greater than 100.

I then created two measures using the following two expressions:

Above100 = COUNTAX(FILTER(SUMMARIZE('Table 2','Table 2'[IsAbove100],'Table 2'[Item]),'Table 2'[IsAbove100]=1),'Table 2'[Item])

Below100 = COUNTAX(FILTER(SUMMARIZE('Table 2','Table 2'[IsAbove100],'Table 2'[Item]),'Table 2'[IsAbove100]=0),'Table 2'[Item])

At this point, we use the columns and months of the calculated table to create a table visual, a slicer, and a card, respectively:

When I select any month in the slicer, it calculates whether the total value of the corresponding item is greater than 100 items.

I've provided the Pbix file below.

 

 

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.