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:
| Role | Region | Product | Opportunity 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 Role | Field | +/- | Value |
| Custom Role 1 | Region | + | Continental Europe |
| Custom Role 1 | Product | + | Product1 |
| Custom Role 1 | Product | + | Product2 |
| Custom Role 1 | Opp Type | + | New Business |
| Custom Role 2 | Region | - | North America |
| Custom Role 3 | Region | + | UKI |
| Custom Role 3 | Region | + | North America |
| Custom Role 3 | Opp 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!!!
