Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rswami_4
Helper I
Helper I

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

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.


.

1 ACCEPTED SOLUTION

@rswami_4

 

Follow this and let me know if this help you:

 

Image1.png




Lima - Peru

View solution in original post

7 REPLIES 7
ankitpatira
Community Champion
Community Champion

@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:output.png

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?

 

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

 

Capture.PNG

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

@rswami_4

 

Follow this and let me know if this help you:

 

Image1.png




Lima - Peru

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.