Forum Discussion
Post PartisanHow to filter metric
How I can filter a measure dynamically based on value. I have hourly data for everyday in my table and I have calculated average for eg average memory usage.
Now I want to give user ability to filter based on period and value of measure.
How I give user option to filter all servers whose average memory utilization is >=90%
I have to give mutiple range option to users.
Hi DiKi-I
My mistake. Does this help?
Include = VAR _Avg = AVERAGEX( 'Memory', 'Memory'[Memory Usage%] ) RETURN IF( _Avg >= [Threshold Value] / 100, 1, 0 )
7 Replies
- gmsamborn
Super User
Hi DiKi-I
In my solution, I did the following:
- In the ‘Memory’ table, I renamed the [Date] column to [DateTime], duplicated it, and split the duplicate into 2 columns: [Date] and [Time]. ([Date] isn’t currently used but would be useful if you require a date table.)
- I created a table (‘Hour’) with a calculated column (‘Hour’[Time]) and created a relationship between ‘Memory’[Time] and ‘Hour’[Time].
- I created a parameter ('Threshold') with a slicer so the user can specify 90% usage for example.
- I created the following 2 measures:
Avg = AVERAGE( 'Memory'[Memory Usage%] ) Include = IF( [Avg] >= [Threshold Value] / 100, 1, 0)- I filtered a table visual using [Include]
I hope this makes sense.
DiKi-IThank you for your solution. I created a date table and created relation with date. The average value is getting filtered at run time which is what I wanted. But at row level your include flag returns 0 instead of 1 but the values are getting returned is the correct average only this flag is creating confusion since it is at row level.
https://drive.google.com/file/d/1qUbMgKfiEv9oTbq5Mn6ofhx-bArGONpI/view?usp=sharing
- DiKi-I
https://drive.google.com/file/d/1u8M2KaMh7nEQ4ajSQ-4OP6ShLEGvFtmh/view?usp=drive_link
sample data attached for reference.
amitchandak @Ibendlin - AllisonKennedy
Community Champion
UPDATE: not sure why I couldn't see it when I posted this, but sounds like gmsamborn has done exactly what I described so their solution should give you good guidance. 🙂
DiKi-I to filter for the period, you'll need a dimension table.
https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power.html
For the average greater than a specific value, you'll need to build this into your measure that you use as a visual level filter or as the measure itself in the visuals. First create a parameter: https://excelwithallison.blogspot.com/2021/04/dax-parameter-single-value-select.html
Then use that parameter value in the measure in an IF statement, for example:
NewMeasure = IF( [parameterValue] < [oldMeasure], [oldMeasure], blank() )
