Apply two-way table of Business Rules across multiple column criteria

[mdalton2100]

Yes, the title of this topic is a bit of a mouthful. Hopefully, the solution won’t be so much! .pbix and source tables have been attached.
Two way lookup across multiple criteria.pbix (157.6 KB)
Rule and Fact tables.xlsx (14.7 KB)

Anyway, I would like to apply the following business rules to the fact table shown further below. Not only do I need to lookup the business rules “two-way” but also apply it across multiple column criteria (in this example, two: Attribute A, Attribute B).

I have simplified my actual data model for purposes of DNA forum (of course), as there are actually four column attributes and about 6 or 8 ‘Category’ columns. And there are over 2,500 rows.

Achieving the following results:

And by the way (fwiw) here is how the expected results table was calculated using standard Excel formulas:

[Heather]

well, I would join the two tables with a combined attribute column - then build out my measures using that

1. I added a “Combined Attributes” column to your Fact Table
2. I changed that to the column used to reference the Rules Table
3. then hid most of the columns on your Fact Table (you don’t want to use those in your visuals)
4. and built the appropriate measures

See attached solution file, I also added a “FileSource” parameter to easily flip between the location of the files on MY computer, back to your own (just use the parameter drop down to switch between file locations)
eDNA Solution Two way lookup across multiple criteria.pbix (161.3 KB)

[mdalton2100]

Much appreciated Heather, thanks!