Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I would like to create a static column or a measure with the following values:
Threshold |
0 |
0.1 |
0.2 |
0.3 |
0.4 |
0.5 |
0.6 |
0.7 |
0.8 |
0.9 |
1.0 |
I would then like to create a slicer with this column or measure and use it as a criteria in a visual level filter. If it is possible, then I would appreciate if someone could explain to me the steps.
.
Solved! Go to Solution.
@rswami_4 Go to power bi desktop query editor.
1. Click Enter Data under Home tab and create table. Close & Apply.
2. Then use that for slicer.
Ankit,
I wish it was that simple. Let me elaborate and clarify my request:
I have a table that contains the following information:
Category | Value |
Cat1 | 0.06 |
Cat3 | 0.27 |
Cat2 | 0.39 |
Cat3 | 0.75 |
Cat3 | 0.2 |
Cat3 | 0.95 |
Cat1 | 0.76 |
Cat3 | 0.25 |
Cat2 | 0.19 |
If I create a column chart to get the average by category, the result looks like this:
Now, how can I use a column or a measure that contains the threshold values from 0 to 1 in the intervals of 0.1, to slice the information and only show the values above or below the selected threshold value in the chart above?
You need some tricky measures in this case. Check more details in the attached pbix.
Avg Above Or EQ Threshold = IF ( ISFILTERED ( Threshod[Threshold] ), AVERAGEX ( FILTER ( 'Table', 'Table'[Value] >= VALUES ( Threshod[Threshold] ) ), 'Table'[Value] ), AVERAGE ( 'Table'[Value] ) )
Avg Below Or EQ Threshold = IF ( ISFILTERED ( Threshod[Threshold] ), AVERAGEX ( FILTER ( 'Table', 'Table'[Value] <= VALUES ( Threshod[Threshold] ) ), 'Table'[Value] ), AVERAGE ( 'Table'[Value] ) )
Eric,
Appreciate your response. The solution you proposed is very interesting and helpful. At least I learned a way to use values of a table as a filter for another table. However, the requirement is slightly different. In your solution, the filtering happens before aggregation by category. What I need is the filtering happening after the aggregation by category. For example, if I choose the above threshold value of 0.4, then only Cat1 and Cat3 with the values of 0.41 and 0.484 should display. similarly, if I choose the below threshold value of 0.4, then only Cat2 with the value of 0.29 should display.
Thanks,
Swami
Worked like a charm. Thanks a lot.
Just for clarification, the solution is:
AboveAvrg =
var avrg = AVERAGE('Table'[Value])
return if(avrg > MAX('Table'[Intervals]),avrg,BLANK())
BelowAvrg =
var avrg = AVERAGE('Table'[Value])
return if(avrg < MAX('Table'[Intervals]),avrg,BLANK())
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |