Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 , Try a new column like
countx(filter(table,table[Sensor Name]=earlier(table[Sensor Name])),[Value])
@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?
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]))
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |