Filtering a large table and find distinct values

[kkanda]

Hi All,

We are analyzing the Dissolved Gas values of electrical substation equipment. Each set of data consists of measurements of 8 different gases and there are about 900 pcs of equipment. The complete data set consists of the measurements for all the equipment for 6 different dates. The format of the list of measurements is as following:

Date	Gas	Measurement Rdg	Equipment No.
1/2/2021	A	90	EX1
1/2/2021	B	130	EX1
1/2/2021	C	220	EX1
1/2/2021	D	2800	EX1
1/2/2021	E	45	EX1
1/2/2021	F	55	EX1
1/2/2021	G	0.2	EX1
1/2/2021	H	356	EX1
1/2/2021	A	110	EX2
1/2/2021	B	78	EX2

The list continues to have about 45000 records.

Each Gas has a limit value. If the Measurement Reading exceeds the limit value of that Gas on any of the six different dates, we want to examine the trend for that Equipment No.  

The problem is to filter the above table for those records exceeding the limit value for each Gas and then finding the distinct Equipment Nos of the last column.

I exported the above table to Excel and worked on successive filtering to find the distinctive Equipment Nos. But I do not know how to do it in DAX or Query Editor.

I need help to write the DAX statements (or Query Editor) to get the filtered table and then extract the distinct Equipment Nos from that table. Thanking you in advance

[amitchandak]

@kkanda , You need to provide the expected output, what limit you want to set. 

a new table

Distinct Equipment No= distinct(Table[Equipment No])

a new Measure

Distinct Equipment No= distinctcount (Table[Equipment No])

a new Measure with filter

Distinct Equipment No= calculate(distinctcount (Table[Equipment No]), filter(Table, Table[Measurement] >100))

[kkanda · Accepted solution]

@amitchandak - Thank you for your response.

I worked on a duplicate table applying the required filters on each of the Gases  through Query Editor and then populated the Equipment Nos in a slicer create a chart.