DAX - operations between two tables with a many-to-many relationship

[charlie77]

Hi

I'm quite desparate with this prorgam dealing with data about two levels of geography - GeoLarge (GL) and GeoSmall (GS). GSs are much smaller than GLs but thier boundaries are not aligned, that is, while a majority of GSs are fully contained by GLs, some GSs cross more than one GLs (partially contained). The data are in three tables as follows

The first table is just a list of GL names to support a slicer for users to select one or more GLs for reproting.

Table 2 is geographic concordance data based on population (Pop) i.e. Pop_GL1 = 100% Pop_GS50 + 70% Pop_GS99 +...+15% Pop_GS200.

Table 3 is the main data based on GeoSmall (GS).

Relatiohsips: 1 to many between Table 1 and Table 2 by GL_Name; Many to many between Table 2 and Table 3 by GS_Name.

The problem- once one or more LG(s) is/are selected (from the slicer), the Data of GSs in Table 3 related to (one of) the selected GL(s) need to be summed with respective concordances in Table 2 as the weight. Because of the set relationships, once a GL is selected, the related (i.e. fully or partially contained) GSs are automatically filtered in Table 3. 

I guess it may get quite complicated in the case of multiple selection of GLs, so to make it simple, let's assume only single selection from the slicer is needed.  

I made a number of attempts including using a interm table but my very limited skill in DAX didn't lead me to anywhere. Any expert advice is much appreciated. 

Thanks

Charlie 

[v-jayw-msft · Accepted solution]

Hi @charlie77 ,

I created some sample data according to your description:

Relationship bewteen them:

Measures I used:

Measure = SUM(Data[Data])*MAX(Concordance[Concordance])

Measure 2 = SUMX(Concordance,[Measure])

The Result would be shonw as below:

Please check if the result achieve your expectation.

If not, please share some sample data and expected result to me if you don't have any Confidential Information.
BTW, Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jayw-msft · Accepted solution]

Hi @charlie77 ,

I created some sample data according to your description:

Relationship bewteen them:

Measures I used:

Measure = SUM(Data[Data])*MAX(Concordance[Concordance])

Measure 2 = SUMX(Concordance,[Measure])

The Result would be shonw as below:

Please check if the result achieve your expectation.

If not, please share some sample data and expected result to me if you don't have any Confidential Information.
BTW, Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[charlie77]

Hi Jay 

Not sure whether it's too late to ask this in this thread - I tried to combine the two measures into one DAX command to make it bit more compact but I got a wrong result:

MEASURE 3 =

    VAR Combined = SUM(Data[Data]) * MAX(Concordance[Concordance])

    RETURN

   SUMX(Concordance, Combined)

Regards

Charlie

[charlie77]

Hi Jay

Thank you for the assistance. Your results are right but somehow I wasn't able to produce the results with the measures. In Measure, why to use Max(Concordance[Concordance]) which always gives 1? 

Best

Charlie

[v-jayw-msft · Accepted solution]

Hi @charlie77 ,

We can say it's a mechanism for Measure.
If we take the Measure alone without any filter, it will calculate whole table.
But if we put Measure together with the rows, it will filter the data of the current row to calculate, unless we use ALL() function to remove the filters.

Please check the measures and screenshot below, might help you to find the difference more directly:

Measure 3 = MAX(Concordance[Concordance])
Measure 4 = CALCULATE(MAX(Concordance[Concordance]),ALL(Concordance))

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[charlie77]

Hi Jay 

Thank you again for the assistance and sharing the knowledge. Yes it's working now with my real data - quite magic. Last time I create the measures in a separete table and this time I created them in the concordance table. I'm still pondering why I can't put the measures anywhere else.  

Regards

Charlie

[charlie77]

This seems to be a tough problem judging by the lack of attention. I think boilling down it's about how to calculate, in a row context, using data from two tables with a many-to-many relationship. I tried to use Related function and then found Related doesn't apply to many-to-many. Any idea on that e.g. bring two columns together in a row-context equation from two tables, doesn't have to be a comprehensive solution, is much appreciated.    

Charlie