Filter calculations based on table of "business rules"

[leighpeterson]

Hi all,

TLDR I would like to be able to include or exclude certain subsets of data by recording the combination of field values in a separate table. Pls advise 🙂

I have a few measures that I need to calculate, mostly sums and distinct counts, in various filter contexts that are specific to certain types of programs and services, and in some cases, particular agencies. For demonstration purposes, I'm using the Global Superstore dataset often used in Tableau training; in this case, let's say I want to exclude rows based on certain segments within certain countries, etc.

Ultimately, I would like the table of business rules to be easily accessible and editable (e.g. in an Excel workbook or SharePoint list) so that users with domain knowledge but not Power BI experience can revise/remove/add to them, and ultimately I would love to have a column (let's call it Keep, containing true and false values) that determines whether to include or exlcude the rule, as in some cases it will be much more succinct to declare a rule for what you don't want and in other cases, it will be much more succinct to declare a rule for what you do want.

I'm open to ideas in term of how to approach this. Focusing on excluding for now, what I have right now (in the file below) is a left antijoin in Power Query, and it's working as expected with the simple test rules I have--basically a record of that contains the combination of values that should be excluded. (this could easily be inverted with an inner join for combinations of values that should be included)

For example, given a table of business rules that looks like

ID	Segment	Country
1	Consumer	United States
2	Corporate	United States
3	Home Office	France

rows where Segment = Consumer and Country = United States etc. will be filtered out. The antijoin is done on the Segment and Country columns.

The problem with that is that it's much more complex in reality and the rows will not be excluded on the same two columns every time; it could be any of up to six or so. So I could do an antijoin on all of the possible columns, but that doesn't seem ideal to me as it could make the business rules table more difficult to maintain--more cases would have to be accounted for.

I would prefer to have a more dynamic Attribute and Value column, if you will, where the Attribute could represent any column and the Value could represent any value from that column. I'm envisioning then that the business rules table could consist of multiple attribute-value rows per rule, maybe something more along the lines of

Rule	Attribute	Value	Keep
US Only Home Office	Country	United States	FALSE
US Only Home Office	Segment	Consumer	FALSE
US Only Home Office	Segment	Corporate	FALSE

The problem here is that this structure long structure won't work in a join with the wide Orders table. I could pivot this into a wide format, but then any attributes not recorded here will have null values, which will not work in a join either.

So it seems like DAX might be a better solution, but I can't figure out what the filter context part of the CALCULATE function would be. It doesn't like it if I try to use the LOOKUPVALUE function to grab a TRUE/FALSE value, and that doesn't really help me anyway since LOOKUPVALUE requires specifying specific columns, which is what I'm trying to avoid. Basically, I'd love to be look at whether a Keep field is TRUE or FALSE for a given combination of Attributes and Values and do something along the lines of

CALCULATE(
  SUM(Orders[Sales]),
  [Rule] = SELECTEDVALUE([Rule]),
  IF([Keep], keep rows where [Attribute] = [Value], keep rows where [Attribute] <> [Value])
)

Obviously this is pseudocode.  Among other problems, [Attribute] should never actually equal [Value] in the way that "Country" does not equal "United States", but hopefully you get what I mean: keep rows where Orders[Country] = "United States". I know that DAX doesn't have an INDIRECT function to pass column references as strings like Excel does, but maybe there's something here?

Otherwise, I'm also open to tackling this in Power Query! I'd probably be more comfortable there anyway as PQ is much more permissive when it comes to dynamic references and the like. But it feels like there's a relatively simple solution that I'm not seeing.

Any ideas? Thank you!!

.pbix here and data sources here https://we.tl/t-wvQFlvuvB1

[parry2k]

@leighpeterson hi there, sorry for this reply after LONG LONG time, this video is almost ready and will be out soon. I will share the link here in case you are still looking for it. Cheers!!

[parry2k]

@leighpeterson I think I figured it out, was exhausting but interesting. I'm going to apply this to your pbix file and then send it to you for testing. 

I think it is so unique, and if it works, it will be worth doing a video on it to explain everything specially the thinking process and how I approached it 🙂 But first thing first, get it in your hand for testing. I'm sure there will be areas of improvement, 1st step is to make it work and go from there. Stay tuned! 

[parry2k]

@leighpeterson Thanks for your patience and for trying to answer all the weird questions. I'm excited to work on it, and no idea where to start but at least I (we) know the end. After finishing some other stuff (recording a video for my channel) I will get to this. Stay tuned! Cheers

[parry2k]

@leighpeterson ok, to make sure we are on the same page. First run exclude rules and then run include rules from the data returned after exclude rules, yeah?

If we have kind of boundaries established, fun starts from here 🙂

[leighpeterson]

@parry2kyes, I think that works. I don't have strong feeling about the order of operations as long as it's intelligible and consistent. thanks again for your attention to this; looking forward to seeing what you come up with!

[parry2k]

@leighpeterson Further thoughts on this, if want to use KEEP for inclusion and exclusion then these rules need to be applied one after another:

For example:

- first, apply where keep = true and get the table that meets the rule requirement

- then apply where keep = false on the table returned after applying the 1st rule

Ofcourse this can be executed in opposite order. 

[leighpeterson]

@parry2kyes, I think we could choose arbitrarily that exclude rules always happen first, for example, or vice versa. suppose there was an include rule that attempted to include rows where Country = USA and Segment = ABC, but those rules were already excluded by a rule, I think it would be fair to expect that the include rule would not work. the responsibility would be on the person developing the business rules to make sure they were logically consistent

[parry2k]

@leighpeterson I expected this reply, using it for both inclusion and exclusion, even before we get to the solution, it is already a show-stopper, let me explain how:

Assuming we matched records with rules data, doesn't matter how.

As per your example:

one rule where country = USA and segment = ABC, and keep = TRUE  -  in the data table there are 10 rows of county USA with 10 different segments. In this case, we will only keep matching rows with the rules, in other words remove the rows where no rule found

2nd rule where country = France and segment = "ABC", "XYZ" Keep = FALSE,  in the data table there are 3 rows with country FRANCE and 3 different segments, two rows matched with the rules = FALSE, in this case, we are removing rows where KEEP = false and keeping the rows where no rule found

If you look at the above 2 examples, in 1st case we are keeping the rows where the rule is found (KEEP = TRUE) or we are removing the rows where the RULE is not found

whereas in 2nd case we are removing the rows where RULE is found or keeping the row where the RULE is not found.

1st and 2nd example is opposite of each other and not sure how logic can be developed to make it work. If the logic is consistent between cases 1 and 2 then we can work with it.

Maybe I'm over thinking, and that's why I mentioned this should be either inclusion or exclusion rules list.

[parry2k]

@leighpeterson if it is the 2nd one, pre-filter the data, it means, rules will have only one logic, either inclusion or exclusion. So basically rules are an exception list either used for inclusion or exclusion, I don't see the value of how it can be used with the mix. Maybe I'm missing something here.

[leighpeterson]

@parry2k it is the second one. users should only see the data with all business rules applied

logically, yes, only inclusion or exclusion is necessary. the reason it would be nice to allow both is for the ease of use of the person determining the rules. if a segment should be filtered for only 1 of 10 sub-categories, it's easier to enter a record to say that only that 1 should be included, instead of entering 9 records to say which should be excluded. if another segment should include 8 out of  10 sub-categories, it's easier to enter 2 records to determine which should be excluded rather than 8 to say which should be included, especially since in some cases, there will be far more than 10 available options for a given field

remember that someone with knowledge of the business rules should be able to create and edit them in an Excel workbook or SharePoint list which will then be queried into Power BI and mashed up with the rest of the data. so the user should know what the business rules are, but they have no reason to look at the data without the rules applied, except maybe in rare cases. the business rules are in the early stages of being developed, and will require refinement, which is why I want to allow easy edit access to them

[parry2k]

@leighpeterson so let me ask about the usability of this solution. you have 10 different rules. Do you want to filter the data upfront based on the rules, and users only see the data after the rules are applied, or the whole data is loaded, and use interact what rule they want to apply and then visually show based on that selection?

From your last reply, it looks like you want the rules to be applied before data gets loaded for the visualization, correct?

[parry2k]

@leighpeterson I think I got an idea 💡

BTW, do you happen to have a sample pbix file to play with? Thanks!

[parry2k]

@leighpeterson thanks for the reply. The first 2 are easy to do, and matter of fact I did something similar.

As you rightly said, the problem is with the 3rd one. I'm still thinking about how it can be done. DAX is the front-runner but the problem is you cannot work with columns dynamically. I believe the user will have a slicer with rules, and based on the slicer selection everything should work.

If there is any hope for any solution, it is going to be data modeling + DAX. I believe no single method is going to work. Again, too much unknown at this point 🙂

Just for assumption purposes, how many rule columns we are planning to work with? Ofcourse there shouldn't be any hardcore limit but still try to know this.

Above all, maintaining of the rules in the unpivoted form is the way to go, it is scalable and easy to manage.

[leighpeterson]

@parry2kI'm not sure about using a slicer on the front end. for all intents and purposes, the user should never see the data without the business rules applied--they are rules about the data should be processed before being aggregated. for that reason and because DAX struggles with using columns dynamically, I'm leaning towards power query.

most rules will be operate on two or three fields, but some may require six or more. I'd like to find a solution that theoretically could work with any number, as that may become necessary

you can download the .pbix and data sources here https://we.tl/t-wvQFlvuvB1

[leighpeterson]

thanks for looking into it @parry2k!

• a rule having multiple rows should use AND logic I suppose. that's how it would work if you were joining on multiple columns. it would be wonderful if I could get it to work so that, if there were multiple rows with the same attribute for the same rule, it would use OR logic between those. so in the example above, [Country] = "United States" AND ( [Segment] = "Consumer" OR [Segment] = "Corporate")
• I haven't thought about that, but I think it would be simpler to have each rule only exclude or only include. if the same Country needed to have one Segment excluded and several Sub-Categories included, that would be two separate rules
• columns will be dynamic, which is exactly the problem, and why I'm hoping to unpivot into Attribute and Value for the rules so that each rule can operate based on a variable number of fields. otherwise, each field that could potentially dictate a rule would have to have a non-null value, which would mean that all possible cases would have to have a record, i.e. if we wanted to include only the "Chairs" Sub-Category for France, we would need a row where [Country] = "France" and Sub-Category = "Chair" for each possible combination of values in the other columns because, as far as I know, we can't successfully join on rows with null values in the join columns. I'm wondering if rather than actually using the Merge feature in Power Query it might be better to just create more complex conditional columns which could then be filtered

[parry2k]

@leighpeterson very interesting question, not sure if I will have the solution at the end or not but I would like to give it a try I have a few questions before that:

- if one rule has multiple rows, does it is an AND condition for all the rows of the rules or an OR condition?

- can one rule have the TRUE and FALSE combination for KEEP, or it is always TRUE or always FALSE for all the rows?

- Are these fixed to two columns Segment and Country or columns will be dynamic (which could be a problem though) 

While I'm thinking how to approach this I would like to hear your feedback on the above questions.