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
Anonymous
Not applicable

How to create custom filter

Hello. I have two tables. First has product id and appropriate value, while the second has countries which have made the trade. So if I select one product the second table shows countries which have also exported the selected product. Now I want a filter that shows values greater than which is input by the user. for example, if the user wants to see total values for each product greater than 10 mln $ so minimum value in the first table must be 10. How can I create that type of filter?

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

hi  @Anonymous 

For your case, you need to create a parameter for a slicer, you could just use what if parameter to get it.

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-what-if

 

Then use this parameter measure as a conditional as below:

Measure = SUMX(FILTER(VALUES('Table'[product id]),[Total values]>[Parameter Value]),[Total values])

If you still have problem, please share your sample pbix file and your expected output. 

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@Anonymous , Assumed you have used a what if parameter, then you can create a measure like this

 

 

measure =
var _max = maxx(allselected(slicer),slicer[paramvalue]) //what if
return
Sumx(Values(Table[product]), if(Sum(Table[Value])>_max,Table[Value],blank()))

 

Link -https://docs.microsoft.com/en-us/power-bi/desktop-what-if

 

 

Sumanth_23
Memorable Member
Memorable Member

hi @Anonymous, You will not be able to create slicers using measures in Power BI. But if you want to build it using a direct numeric column - you can use the approach as seen in the screen grabs

 

Country Prod Table.jpg

 

Revenue Table.jpg

 

Revenue Slicer 2.jpg

 

Revenue Slicer.jpg

 

Please mark the post as a solution if my comment helped with solving your issue. Thanks! 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



negi007
Community Champion
Community Champion

@Anonymous  Hi in this case you will need to create a calculated table like belwo for two slicer values

 

Step 1:

Data_Type_Selected = {
(1,">10 Mio"),
(2,"<10 mio")
}
 
Create your measure that will filter values basis your selection
Step 2: 
Data_type_Filter_value =
IF(HASONEFILTER(Data_Type_Selected[Selection]),
SWITCH(SELECTEDVALUE(Data_Type_Selected[Selection]),
">10 mio", SUM(product value),
"<10 mio", SUM(product value),
),
BLANK()
)
 
In case you are not able to perform above steps, please share your sample data in text format.



Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

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.