cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
charlie77
Helper II
Helper II

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

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

111.jpg

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 

 

 

 

 

 

2 ACCEPTED SOLUTIONS
v-jayw-msft
Community Support
Community Support

Hi @charlie77 ,

 

I created some sample data according to your description:

3.PNG1.PNG2.PNG

Relationship bewteen them:

4.PNG

Measures I used:

Measure = SUM(Data[Data])*MAX(Concordance[Concordance])
Measure 2 = SUMX(Concordance,[Measure])

The Result would be shonw as below:

5.PNG6.PNG7.PNG

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

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))
8.PNG
 

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

6 REPLIES 6
v-jayw-msft
Community Support
Community Support

Hi @charlie77 ,

 

I created some sample data according to your description:

3.PNG1.PNG2.PNG

Relationship bewteen them:

4.PNG

Measures I used:

Measure = SUM(Data[Data])*MAX(Concordance[Concordance])
Measure 2 = SUMX(Concordance,[Measure])

The Result would be shonw as below:

5.PNG6.PNG7.PNG

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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

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

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))
8.PNG
 

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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
Helper II
Helper II

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

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors