Always include certain rows (across multiple fact tables) based on a single (selected) condition

[KierenPorter]

I am attempting a new modelling structure to negate my current method of repeating (always to be included) rows in my dataset for every "Design Option", i.e.

where the red-highlighted items are repeated purely to "appear" when using a slicer across the 'DOID' column.

so, in my test pbix file, I've built a calculated "slicer" table :

Option = FILTER(ALLSELECTED(tDO[DOID]),(tDO[DOID])<>0)

and a measure that looks up the "Design Option" in my tDO dim table:

Fetch = IF(SELECTEDVALUE(tDO[DOID])=0||SELECTEDVALUE(tDO[DOID]) in VALUES('Option'[DOID]),1,0)

along the lines of the advice offered in this thread 

...and having built 2no. sum measures for my tPeople & tItems fact tables respectively,

sumAge = CALCULATE(SUM(tPeople[Age]),FILTER(tPeople,tPeople[DOID]=SELECTEDVALUE('Option'[DOID])))+CALCULATE(SUM(tPeople[Age]),FILTER(ALLSELECTED(tPeople),(tPeople[DOID])=0))

sumPrice = CALCULATE(SUM(tItems[Price]),FILTER(tItems,tItems[DOID]=SELECTEDVALUE('Option'[DOID])))+CALCULATE(SUM(tItems[Price]),FILTER(ALLSELECTED(tItems),(tItems[DOID])=0))

 all is working kinda well: I get the values I want when interacting with the "Option" slicer:

except, looking further down the line (when I attempt to roll this concept into a dataset that will have 30+ fact tables, each with a  "Design Option" column) having to add the "Fetch" measure (as a =1 filter) to EVERY visual in the future has me doubting this method's scaleability.

I understand we can't add measures as a page level filter (which was my first instinct) but perhaps there's a next-step (from where I am presently) that I'm not seeing? Or a sounder logic I've not yet considered?

I have wondered if there's a way to entirely "clone" my fact tables using the calculated (filtered) method (since I read somewhere we can't use SELECTEDVALUE in a calculated column?) but came up short attempting that. At the moment I'm a little stuck in "Excel-SUMIFS-think" and not sure how to approach things in DAX.

Any and all guidance greatly appreciated, thanks!

[KierenPorter]

picking this up again (got distracted with other things) and thinking I perhaps need to reframe my question, so as per below:
I have the blue table - and want to be able to get to the green and orange tables - where the row items 1,2 & 3 are summed with items of matching criteria (here only [ZONE] but in future additional others)

...so if/when seen in a column visual, the (yellow) values of -- wouldn't be seperate, but repeated/included in the columns of Aa,Ab & Ac:

any and all help much appreciated! 🤞

[KierenPorter]

Hi V, thanks for stopping by!

Please find this link to a OneDrive folder holding both .pbix and .xls datasource (the latter added just incase) 

RepeatingRows

I'm not sure how to articulate my desires any better than:

any rows in any fact table (in my example file, people or items) where  "design option" column [DOID] = 0 are to be considered "always present" and subsequently ALWAYS show/sum/etc WITH results of any slicer applied to the [DOID] values.

...perhaps think of rows w/ [DOID]=1 as spectacles, an essential item (for someone short-sighted like me!) where rows [DOID] NOT 0 are a one-time choice of clothes? (not sure that metaphor helps! 🤣)

Since posting yesterday I experimented some more - and ran into another roadblock, highlighting further complication with my current method - the [DOID]=0 values are doubling up (see the donut) girls emma (18) + katie (11) should sum to 29, but are summing w/ kieren (27) to 56... when Kieren (27) is (also, and only) a boy!

[Anonymous]

Hi @KierenPorter ,

Calculated tables are generated during data refresh and are physically stored in your model.They are static until the data is refreshed again. So the calculated tables cannot dynamically change based on slicer selections. Instead of creating a separate calculated table, consider using a measure that calculates the desired result.

Could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards