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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
mostvp123
Advocate V
Advocate V

Apply Custom Filtering, Additive/Subtractive Based on External User Input

Hi all! I have a Power BI challenge which I just can't figure out.
I have a report where we are calculating how much money needs to be paid to a set of individuals who work on opportunities in our CRM. Depending on the individuals' roles, they will be paid based on a different set of rules. 

Now, the rules are preset for each role, but we have some individuals who follow "Custom" roles. The rules behind these roles are  stored in an external database, in the following format. The custom roles are then assigned to individuals. The idea is to use these rules as a set of simple filters, on predefined fields in the CRM Opportunity table, which either a) leave the field untouched b) include certain values for that field c) exclude certain values for that field.
The data would look something like this:

RoleRegionProductOpportunity Type
Custom Role 1"+Continental Europe""+Product1", "+Product2""+New Business"
Custom Role 2"-North America"  
Custom Role 3"+UKI", "+North America" "-Existing Business"


The data can change at any time, and there are more than the 3 columns above. We can change the way data is input if necessary, the individuals who input the data understand the structure has to be respected for Power BI to work. All filters would be applied to the same data table - Opportunity. If a cell is empty that means no filters should be applied to that field i.e. include "all" is to be applied.

Translating the intended effect of the sample rules for each Role into a sentence:
Custom Role 1: Region = Continental Europe, Product = Product1 or Product 2, Opportunity Type = New Business
Custom Role 2: Region <> North America, include all products and all opportunity types
Custom Role 3: Region = UKI or North America, all products, Opportunity Type <> Existing Business

Using the query editor, I've already converted via unpivot and split into rows by delimiter, the table above into the following format:

Custom RoleField+/-Value
Custom Role 1Region+Continental Europe
Custom Role 1Product+Product1
Custom Role 1Product+Product2
Custom Role 1Opp Type+New Business
Custom Role 2Region-North America
Custom Role 3Region+UKI
Custom Role 3Region+North America
Custom Role 3Opp Type-Existing Business

Lets call this table "Custom Role Rules".

How can I use the above data to filter my data to get the intended effect? I've already built some sort of solution by trying converting this into a set of filter statements, inside a loop but I'm falling short.
I'm essentially iterating through each individual, with a SUMX loop - there is a relationship between the individual's assigned role, and a dimensional table containing each role (Many to One, set to crossfilter "Both"), which is then related to the table above (One to Many). So I'm able to pick up the data above in my loop in some variables with CACULATETABLE. There is also a one to Many relationship between my individuals and the opportunity data, so a sum inside the loop can easily pick up the opportunity value.

So, the simple formula below would work, if I knew what filters to apply in my Calculate statement..... 

SUMX(Individual Data,
var RegionValuesAdd = CALCULATETABLE(VALUES('Custom Role Rules'[Value]), Field = "Region", Add/Remove = "+")
var RegionValuesRemove = CALCULATETABLE(VALUES('Custom Role Rules'[Value]), Field = "Region", Add/Remove = "-")
var ProductValuesAdd = CALCULATETABLE(VALUES('Custom Role Rules'[Value]), Field = "Product", Add/Remove = "+")
var ProductValuesRemove = CALCULATETABLE(VALUES('Custom Role Rules'[Value]), Field = "Product", Add/Remove = "-")
var OppTypeValuesAdd = CALCULATETABLE(VALUES('Custom Role Rules'[Value]), Field = "Opp Type", Add/Remove = "+")
var OppTypeValuesRemove = CALCULATETABLE(VALUES('Custom Role Rules'[Value]), Field = "Opp Type", Add/Remove = "-")

CALCULATE( SUM(Opportunity[Value]),
     Region IN RegionValuesAdd && NOT(Region IN RegionValuesRemove),
     Product IN ProductValuesAdd && NOT(Product IN ProductValuesRemove) ,
      OppType IN OppTypeAdd && NOT(OppType IN OppTypeRemove)
 )
)

The trouble is, if I always specify all the fields in my Calculate statement, what to do with the fields which aren't to be filtered?? Say Product has no filters specified (include all), then my ProductValuesAdd/ProductValuesRemove variables would be empty... and this would have the effect of Product IN { Blank() } && NOT(Product IN { BLANK() } ), which returns BLANK() for my calculate statement. Is there a better way of doing this? Some type of concatenation maybe? 

Thanks so much for your help!!!

2 REPLIES 2
v-shex-msft
Community Support
Community Support

Hi @mostvp123,

Power bi does not support this type of filter affect, it did not able to recognize the filters that described with natural language.
You can add some complex Dax expression to extract the field text string, split them to different part and use in conditions but they are not able to be worked as common filer and interaction with relationships. (these expression filters can only be used on the visual level filter)

Regards,

Xiaoxin Shegn

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin, thanks for your reply. As mentioned in the post I've already extracted the string values and applied as filter in DAX expression (see my SUMx formula), I'm not trying to use natural language or visual level filters. Please can you recheck the post? I know this isn't going to be a simple solution, I'm happy to implement a complex DAX measure. The issue is, as mentioned at the end of my post:

"The trouble is, if I always specify all the fields in my Calculate statement, what to do with the fields which aren't to be filtered?? Say Product has no filters specified (include all), then my ProductValuesAdd/ProductValuesRemove variables would be empty... and this would have the effect of Product IN { Blank() } && NOT(Product IN { BLANK() } ), which returns BLANK() for my calculate statement. Is there a better way of doing this? Some type of concatenation maybe? "

Thank you for your help!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.