many-to-many relationship or filtering problem

Mickey Mouse

New Member
Joined
Jan 22, 2013
Messages
2
Hello Excel-Gurus,

I am struggling with PowerPivot and could not find an answer or solution for my problem searching the net.
It has to do with a many-to-many relationship and trying to filter a pivot table by two columns combined with an or and not and but let me give a short example.

TABLE1
IDKST-USERKST-OWNER
11011
22010
31010
43030

<tbody>
</tbody>

TABLE2
KST-IDKST-NAME
10A
11A
20B
30C

<tbody>
</tbody>

I have two relationships to get the KST-NAME:
TABLE1[KST-USER] - TABLE2[KST-ID] (n:1)
TABLE1[KST-OWNER] - TABLE2[KST-ID] (m:1)

I would like to have a PowerPivotTable with the following content:
COUNTA[ID]
A3
B1
C1
Total4

<tbody>
</tbody>
COUNTA[A] counts/shows the rows, which belongs to the KST-NAME in KST-USER OR KST-OWNER.

It should display double clicking the number 3 for example all the rows belonging to the KST-A:
IDKST-USERKST-OWNER
11011
22010
310
10

<tbody>
</tbody>

I already read the blog-posts from Marco Russo and Alberto Ferrari as well as the one from gbrueckl
Resolving Many to Many relationships leveraging DAX Cross Table Filtering « Gerhard Brueckl's BI Blog

I start playing around with LOOKUPVALUE and USERELATIONSHIP in DAX but could not figure out a way to solve my problem.

"Relationships are always equivalent to left outer joins (see MDX and DAX topics: The Logic behind the Magic of DAX Cross Table Filtering ), and context propagates automatically in one direction."
So is there a solution or is it not possible so solve this with Excel and PowerPivot?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
I don't know that you could ever get the drill through feature to give that result. If it's possible, it's beyond me.

However, I do know a way to get you the counts you would like in your pivot using the 2 relationships you have already created.

Create these measures in this order:
[USER Cnt]:=CALCULATE(COUNT('Table1'[ID]), USERELATIONSHIP('Table1'[KST-USER],'Table2'[KST-ID]))

[OWNER Cnt]:=CALCULATE(COUNT('Table1'[ID]), USERELATIONSHIP('Table1'[KST-OWNER],'Table2'[KST-ID]))

[Total ID Cnt]:=[USER Cnt]+[OWNER Cnt]

[USER KST-NAME]:=CALCULATE(IF(HASONEVALUE('Table2'[KST-NAME]),VALUES('Table2'[KST-NAME]),blank()),USERELATIONSHIP('Table1'[KST-USER],'Table2'[KST-ID]))

[OWNER KST-NAME]:=CALCULATE(IF(HASONEVALUE('Table2'[KST-NAME]),VALUES('Table2'[KST-NAME]),blank()),USERELATIONSHIP('Table1'[KST-OWNER],'Table2'[KST-ID]))

[Duplicate Cnt]:=If([USER KST-NAME]=[OWNER KST-NAME], COUNT('Table1'[ID]),0)

[Record Cnt]:=IF([Total ID Cnt]<>[Duplicate Cnt], [Total ID Cnt]-[Duplicate Cnt], If([Total ID Cnt]<>0,1, blank()))

Use KST-NAME in your pivot rows and [Record Cnt] in your values.

I'm sure there are more streamlined and elegant solutions possible, but this one definitely works (at least with your sample data).

As far as the drill through, could you do it another way? What if after you have the basic pivot with KST-NAME and [Record Cnt] set up, you add ID, KST-USER, and KST-OWNER to Row Labels below KST-NAME.

Then make every row field except KST-NAME a tabular layout by changing the settings in Layout & Printing under Field Settings for each field. You will probably need to turn off Subtotals for each of those rows too. Then you will get a pivot showing A, B, & C with plus signs that can be expanded to show the underlying records for each.
 
Last edited:
Upvote 0
I've been batting this one around in the back of my head all day because for 1, I hate how messy my earlier "solution" was and 2, depending on slicers used and everything else, it will probably not work for every scenerio.

Then, as I was watching TV, it dawned on me that this is really another variation on banding.

As such, I think it is solved with a single measure. First however, you must remove all relationships. There should be no relationships between your 2 tables.

After you remove relationships, try the following measure:
[Measure]:=CALCULATE(COUNTROWS('Table1'), FILTER('Table1', 'Table1'[KST-USER] = MAX('Table2'[KST-ID]) || 'Table1'[KST-USER] = MIN('Table2'[KST-ID]) || 'Table1'[KST-OWNER] = MAX('Table2'[KST-ID]) || 'Table1'[KST-OWNER] = MIN('Table2'[KST-ID]) ))

I'm pretty sure this works with KST-NAME in Rows and the measure in values. Plus, it's much simpler to deal with than that mess I posted above.
 
Last edited:
Upvote 0
Thank you for the effort you put into this problem. I tried the second solution and it work for the example data I posted above but as soon as you extend this for example with the following row it will not give the expected result.
IDKST-USERKST-OWNER
11010
22011
31012
43030
51012

<tbody>
</tbody>

The result will be:
Measure
A3
B1
C1
Total4

<tbody>
</tbody>
and not
Measure
A4
B1
C1
Total4

<tbody>
</tbody>

I have had the same problem and was about to start pulling my hair out if I didn't get this to work.
I am now using the following workaround:
Code:
SELECT
  KST-ID
  ,KST-USER KST_VARIOUS
FROM
  TABLE1
UNION
SELECT
  KST-ID
  ,KST-OWNER
FROM
  TABLE1

Using this table with a relationship to table1, a LOOKUPVALUE for KST-NAME and a DISTINCTCOUNT on KST-ID will give me the result I was looking for.

If you have a better or more elegant solution please to not hesitate to post it here.
 
Upvote 0
I guess, I'm a little unclear about what the result needs to be. If I add the additional record to table1, I get this result in my pivot:

Row LabelsMeasure
A4
B1
C1
Grand Total5

<colgroup><col style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;" width="92"> <col style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;" width="72"> <tbody>
</tbody>



Showing that A has 4 record matches, B has 1, and C has 1. The grand total shows 5 which indicates there are 5 unique records being matched to the various names.

Either way, if you have the access and knowledge to manipulate the underlying tables in SQL, that is probably the easiest solution. I wouldn't necessarily write it off as a work around, often times that is the fastest and easiest way to solve a problem, but many PowerPivot users don't have the permissions or know-how to do so.

<tbody>
</tbody><colgroup><col><col></colgroup>
 
Upvote 0
If you can get table1 into this format with SQL then your solution becomes very simple:

IDKST-IDKST-TYPE
110USER
220USER
310USER
430USER
510USER
111OWNER
210OWNER
310OWNER
430OWNER
512OWNER

<colgroup><col style="width: 48pt;" width="64"> <col style="width: 87pt; mso-width-source: userset; mso-width-alt: 4242;" width="116"> <col style="width: 101pt; mso-width-source: userset; mso-width-alt: 4900;" width="134"> <tbody>
</tbody>


Just set up one relationship between the 2 KST-ID columns. Drop KST-NAME into your pivot rows and create the measure =DISTINCTCOUNT('Table1'[ID]).
 
Upvote 0
A possible solution could be the following:
Code:
[Record Cnt] :=
SUMX (
    Table2,
    COUNTROWS (
        FILTER (
            ALL( Table1 ), -- You need ALL only if you have active relationships
            Table1[KST-USER] = Table2[KST-ID]
            || Table1[KST-OWNER] = Table2[KST-ID]
        )
    )
)
Please note relationships are not required to perform this calcolation, you can remove ALL in case you don't have relationships between Table1 and Table2. Performance are not excellent, but if it is fast enough for you this is probably the simpler solution. The outer SUMX is there only to perform calculation over groups of rows selected in Table2 - however, it would sum rows for different names, if you want to obtain a distinct SUM you should use:
Code:
[Record Cnt] :=
COUNTROWS (
    FILTER (
        Table1,
        COUNTROWS ( 
            FILTER( 
                Table2,
                Table1[KST-USER] = Table2[KST-ID]
                || Table1[KST-OWNER] = Table2[KST-ID]
            )
        ) > 0
    )
)
Hope it helps.
 
Upvote 0
As would be expected from one of the true experts on the subject, Marco's 2nd measure is the elegant version of what I was trying to accomplish.

Marco, I don't know if this is ultimately the solution that Mickey is looking for or not, but it is what I was trying to accomplish, so either way you helped me with your examples.

Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top