Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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 )
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() )
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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.
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
Hi @DiKi-I
My mistake. Does this help?
Include =
VAR _Avg =
AVERAGEX(
'Memory',
'Memory'[Memory Usage%]
)
RETURN
IF( _Avg >= [Threshold Value] / 100, 1, 0 )
Yes this is working ang giving expected result thank you for your help.
https://drive.google.com/file/d/1u8M2KaMh7nEQ4ajSQ-4OP6ShLEGvFtmh/view?usp=drive_link
sample data attached for reference.
@amitchandak @Ibendlin
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
60 | |
55 |
User | Count |
---|---|
183 | |
111 | |
105 | |
77 | |
70 |