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
<tbody>
</tbody>
TABLE2
<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:
<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:
<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?
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 |
<tbody>
</tbody>
TABLE2
KST-ID | KST-NAME |
10 | A |
11 | A |
20 | B |
30 | C |
<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] | |
A | 3 |
B | 1 |
C | 1 |
Total | 4 |
<tbody>
</tbody>
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 |
<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?