Using DAX to sum values from multiple levels of 1-to-many entities in Tabular Model

[Markyboy944]

I have a challenge where I need to create calculated columns, within a tabular model, so our Power BI person can use them, in particular for slicers etc, so I cant really use measures. They need to go on the on the Survey entity (the top most entity on the data structure shown). These calculated columns equate to the quantity of electrical components, and the quantity of fixing components (lets call these two calculated columns cc_Qty_Electrical, and cc_Qty_Fixing). The rules for calculating these are shown later, and the actual way this data is organised isn’t trivial.

I have the ability to add calculated columns on the survey entity [which is where I want to put the, and if necessary, I have the power to add calculated columns on the other, lower level entities, but I cant change much else, because the data structure is the back end of a bespoke survey tool, which is provided by an external vendor.

The entities have cascading 1:many relationships, as depicted on the diagram.

The challenge is that the determination of whether a component is electrical, or fixing, can only be done my looking at combinations of values which belong in all of the cascading entities. The actual combinations are “known” and wont change, so in theory can be hard-coded into the relevant DAX if needed.

Rather than explain why, I think it’s best if I just illustrate with some examples of combinations which relate to “Electrical” components (fixing works in the same way, it just has different combinations).

(Note *1) For the actual quantity, this is represented in”AD_Answer_Text” but is (frustratingly) stored as text with values which go “01”,”02”,… etc… “10” (i.e. they are text, but they equate to 1,2,…. 10)

So, lets take the following data scenario

There are several thousand records on the survey entity, …. but for a GIVEN Survey (1332), lets imagine the following set of records. (Note – I have purposely just illustrated the data which relates to “Electrical”. There will be several other sets of records “hanging” off Survey 1332 which aren’t “Electrical”.

Conceptually, the DAX functionality needs to detect the various combinations shown, and convert the AD_Answer_Text to an actual integer, and sum up the values. (So in this example, the actual quantity of “electrical components would be  (2+5+3+4+1) = 15, which would go into cc_Qty_Electrical on the SURVEY entity.

I am pretty sure that the functionality needs to use the “RELATEDTABLE” dax functionality (because this allows a table to be “yielded” from lower level child entities (i.e – it goes from the 1 end to the many end).

What I can’t get my head round is, how to chain these together, either in a single large DAX query with multiple RELATEDTABLE calls.

I have even tried creating some interim calculated columns at the lower-level entities, but am hitting all sorts of problems.

If someone would be kind enough to suggest a strategy, I would be most grateful.

[johnt75 · Accepted solution]

One possible solution is to use the known combinations as parameters for TREATAS, e.g.

cc_Qty_Electrical =
CALCULATE (
    SUMX (
        RELATEDTABLE ( 'Answer_details' ),
        VALUE ( 'Answer_details'[AD_Answer_text] )
    ),
    TREATAS (
        { ( 1, 4, 8, 3 ), ( 1, 7, 6, 2 ) },
        'Response_header'[RH_Type],
        'Section_header'[SH_Section_number],
        'Answer_header'[AH_Answer_number],
        'Answer_details'[AD_Answer_code]
    )
)

[Markyboy944]

Many thanks indeed. Life saver ! 😉