How to filter metric

[DiKi-I]

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.

[AllisonKennedy]

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() )

[gmsamborn]

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.

Filter metric.pbix

[DiKi-I]

Thank 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

[gmsamborn · Accepted solution]

Hi @DiKi-I 

My mistake.  Does this help?

Include = 
VAR _Avg =
    AVERAGEX(
        'Memory',
        'Memory'[Memory Usage%]
    )
RETURN
    IF( _Avg >= [Threshold Value] / 100, 1, 0 )

Filter metric - Dims.pbix

[DiKi-I]

Yes this is working ang giving expected result thank you for your help.

[DiKi-I]

@lbendlin 

[DiKi-I]

https://drive.google.com/file/d/1u8M2KaMh7nEQ4ajSQ-4OP6ShLEGvFtmh/view?usp=drive_link

sample data attached for reference. 
@amitchandak  @Ibendlin