Forum Discussion
Filtering a large table and find distinct values
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 - 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.
2 Replies
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))
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.
