Reporting on Many to many

[mmoizk]

I have 3 datasets DS1, DS2 & DS3. 

Example 

DS1

DS1ID          Name                 DS2ID

1                   T1                         1

1                   T1                         2

1                   T1                         3

2                   T2                         4

2                   T2                         3

2                   T2                         5

3                   T3                         1

3                   T3                         6

DS2

DS2ID          DS2Name             

1                   S1

2                   S2

3                   S3

4                   S4

5                   S5

6                   S6

DS3

DS2ID          PName

1                   p1

1                   p2

2                   p3

2                   p4

3                   p1

3                   p3

4                   p5

4                   p6

5                   p2

5                   p3

Now the report need to count all Trans in DS1 for a Giver PName

PName           Count of DS1.Name

p1                         3

p2                         1

..                          ...

[ankitpatira]

@mmoizk It is simple. You should have your relationships that looks like below. In any visual select name from TAB1 and Count of PName from TAB3.

[mmoizk]

Thanks Ankit Patira, I wish it was that easy . I did extactly that but when i pic something from the 3rd table i get an error. 

[ankitpatira · Accepted solution]

@mmoizk Which visual are you using ? When you pick a field from third table have you specified you want the count instead of Don't summarize (if you're using table).

[mmoizk]

i was using a table visual which was erroring. If i use any other chart with a measure of sum or count i am able to report on the 3rd table. Thanks

[Sean]

@mmoizk can you explain how you get your totals? I get other totals with your setup???

[mmoizk]

Hi Sean, Sorry i didn't mention this earlier but count are actually Distict count of DS1ID .

[Sean]

@mmoizk I still get different results - but all you need is a simple distinct count measure.

Distinct Names = DISTINCTCOUNT(DS1[Name])

EDIT: If your setup is the same it should work.