How to create a static column or measure and use it in slicers and visual filters

[rswami_4]

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.

.

[ankitpatira]

@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.

[rswami_4]

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?

[Eric_Zhang]

@rswami_4

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

[rswami_4]

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

[Vvelarde · Accepted solution]

@rswami_4

Follow this and let me know if this help you:

[rswami_4]

Worked like a charm.  Thanks a lot.

[rswami_4]

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