Create a Calculated Column RowCount for filtering data

[cyclist007]

Hi,

I have a use case where i have data for multiple sensors (for multiple days). In Power BI, I would like to plot this as a trend of sensor readings (values). One issue i have with the data is on certain days some of the sensors are not used but still have a few readings (which would count as anomolies). Normally only if there are more than say 5 readings on a particular day the trend would be useful to plot and see.

I would like to write the logic for the calculated column which has thr row count for that sensor and that date, so that i can use this column and filter out all dates & sensors which have less than say 5 readings.

Example:

Sensor Name	Date-Time	Value	CalculatedColumn
Sensor 1	04-29-2020 3:00am	1.5	2
Sensor 1	04-29-2020 4:00am	1.75	2
Sensor 2	04-29-2020 1:00am	1.5	6
Sensor 2	04-29-2020 2:00am	1.65	6
Sensor 2	04-29-2020 3:00am	1.75	6
Sensor 2	04-29-2020 4:00am	1.85	6
Sensor 2	04-29-2020 5:00am	1.75	6
Sensor 2	04-29-2020 6:00am	1.65	6
Sensor 3	04-29-2020 1:00am	1.5	7
Sensor 3	04-29-2020 2:00am	1.65	7
Sensor 3	04-29-2020 3:00am	1.75	7
Sensor 3	04-29-2020 4:00am	1.85	7
Sensor 3	04-29-2020 5:00am	1.75	7
Sensor 3	04-29-2020 6:00am	1.65	7
Sensor 3	04-29-2020 7:00am	1.62	7

From the above table if i just put a filter saying calculatedcolumn >5 it would filter out Sensor 1 data on chart/visual.

I have tried using 

Reading Count Column = COUNTROWS(FILTER(Query1, Query1[Sensor]=Query1[Sensor] & Query1[LocalTime by Date]=(Query1[LocalTime by Date])))

but it does not seem too work.

Would appreciate if someone can suggest what am I doing wrong. Thanks

[amitchandak]

@cyclist007 , Try a new column like

countx(filter(table,table[Sensor Name]=earlier(table[Sensor Name])),[Value])

[cyclist007]

@amitchandak, Thanks for quick reply. It seems to be working, did not know about "Earlier" and its usefulness!

I have one more question  to do this completely: I need to filter (group by) multiple columns: Just not sensor_name, even the date (& possibly one other field: location) .... & does not seem to work. Can you suggest how multiple filter critera can be added with earlier?

[amitchandak · Accepted solution]

@cyclist007 

Try like

countx(filter(table,table[Sensor Name]=earlier(table[Sensor Name]) && table[Date]=earlier(table[Date])),[Value])

Have a date column that will make it easy

Date = [date-time].date

or

Date = date(year( [date-time]),Month( [date-time]),Day( [date-time]))

[cyclist007]

Thanks @amitchandak that worked (and thanks for adding the extra tip on using .date)

Just one other thing is there any drawback/advantage in using Countrows instead of CountX in this case? I have read Countrows is supposed to be more efficent/better from a performance perspective.