many-to-many relationship or filtering problem

[Mickey Mouse]

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
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]ID[/TD]
[TD]KST-USER[/TD]
[TD]KST-OWNER[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]30[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]

TABLE2
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]KST-ID[/TD]
[TD]KST-NAME[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]C[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]COUNTA[ID][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]
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:
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]ID[/TD]
[TD]KST-USER[/TD]
[TD]KST-OWNER[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10
[/TD]
[TD]10
[/TD]
[/TR]
</tbody>[/TABLE]

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?

 

[MD610]

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.

 

[MD610]

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.

 

[Mickey Mouse]

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.
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]ID[/TD]
[TD]KST-USER[/TD]
[TD]KST-OWNER[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]30[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]10[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

The result will be:
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD][/TD]
[TD]Measure[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]
and not
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD][/TD]
[TD]Measure[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

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:

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.

 

[MD610]

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:

[TABLE="width: 123"]
<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>[TR]
[TD="width: 92"]Row Labels[/TD]
[TD="width: 72"]Measure[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]Grand Total[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]



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.
[TABLE="width: 164"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[MD610]

If you can get table1 into this format with SQL then your solution becomes very simple:

[TABLE="width: 236"]
<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>[TR]
[TD="class: xl68, width: 64"]ID[/TD]
[TD="class: xl69, width: 116"]KST-ID[/TD]
[TD="class: xl70, width: 134"]KST-TYPE[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]1[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]10[/TD]
[TD="class: xl70, width: 134"]USER[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]2[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]20[/TD]
[TD="class: xl70, width: 134"]USER[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]3[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]10[/TD]
[TD="class: xl70, width: 134"]USER[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 64"]4[/TD]
[TD="class: xl72, width: 116"]30[/TD]
[TD="class: xl70, width: 134"]USER[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 64"]5[/TD]
[TD="class: xl72, width: 116"]10[/TD]
[TD="class: xl70, width: 134"]USER[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]1[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]11[/TD]
[TD="class: xl67, width: 134"]OWNER[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]2[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]10[/TD]
[TD="class: xl67, width: 134"]OWNER[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]3[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]10[/TD]
[TD="class: xl67, width: 134"]OWNER[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 64"]4[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]30[/TD]
[TD="class: xl67, width: 134"]OWNER[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 64"]5[/TD]
[TD="class: xl72, width: 116"]12[/TD]
[TD="class: xl67, width: 134"]OWNER[/TD]
[/TR]
</tbody>[/TABLE]


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]).

 

[marco.russo]

A possible solution could be the following:

[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:

[Record Cnt] :=
COUNTROWS (
    FILTER (
        Table1,
        COUNTROWS ( 
            FILTER( 
                Table2,
                Table1[KST-USER] = Table2[KST-ID]
                || Table1[KST-OWNER] = Table2[KST-ID]
            )
        ) > 0
    )
)

Hope it helps.

 

[MD610]

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!