Results 1 to 8 of 8

many-to-many relationship or filtering problem

This is a discussion on many-to-many relationship or filtering problem within the Power BI forums, part of the Question Forums category; Hello Excel-Gurus, I am struggling with PowerPivot and could not find an answer or solution for my problem searching the ...

  1. #1
    New Member
    Join Date
    Jan 2013
    Posts
    2

    Default many-to-many relationship or filtering problem

    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
    ID KST-USER KST-OWNER
    1 10 11
    2 20 10
    3 10 10
    4 30 30

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

    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]
    A 3
    B 1
    C 1
    Total 4
    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:
    ID KST-USER KST-OWNER
    1 10 11
    2 20 10
    3 10
    10

    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?

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Indianapolis, IN
    Posts
    188

    Default Re: many-to-many relationship or filtering problem

    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 by MD610; Jan 23rd, 2013 at 12:23 PM.

  3. #3
    Board Regular
    Join Date
    Feb 2012
    Location
    Indianapolis, IN
    Posts
    188

    Default Re: many-to-many relationship or filtering problem

    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 by MD610; Jan 23rd, 2013 at 09:24 PM.

  4. #4
    New Member
    Join Date
    Jan 2013
    Posts
    2

    Default Re: many-to-many relationship or filtering problem

    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.
    ID KST-USER KST-OWNER
    1 10 10
    2 20 11
    3 10 12
    4 30 30
    5 10 12

    The result will be:
    Measure
    A 3
    B 1
    C 1
    Total 4
    and not
    Measure
    A 4
    B 1
    C 1
    Total 4

    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.

  5. #5
    Board Regular
    Join Date
    Feb 2012
    Location
    Indianapolis, IN
    Posts
    188

    Default Re: many-to-many relationship or filtering problem

    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 Labels Measure
    A 4
    B 1
    C 1
    Grand Total 5



    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.

  6. #6
    Board Regular
    Join Date
    Feb 2012
    Location
    Indianapolis, IN
    Posts
    188

    Default Re: many-to-many relationship or filtering problem

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

    ID KST-ID KST-TYPE
    1 10 USER
    2 20 USER
    3 10 USER
    4 30 USER
    5 10 USER
    1 11 OWNER
    2 10 OWNER
    3 10 OWNER
    4 30 OWNER
    5 12 OWNER


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

  7. #7
    New Member marco.russo's Avatar
    Join Date
    Dec 2012
    Location
    Europe
    Posts
    38

    Default Re: many-to-many relationship or filtering problem

    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.

  8. #8
    Board Regular
    Join Date
    Feb 2012
    Location
    Indianapolis, IN
    Posts
    188

    Default Re: many-to-many relationship or filtering problem

    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!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com