How to make this data connection?

[evgeniystuchalk]

Hello! I have three tables:

Deals: all deals connected to it's company

Deal ID	Company
1	1
2	1
3	2
4	2

Companyes:

Company ID
1
2

Notes: each company have some notes

Note ID	Company ID
1	1
2	1
3	2
4	2

In m app i have a measure: count([DISTINCT] [Note ID]), that shows me amount of notes, that related to company.

What i want: I want see amount of notes, that related to company, when i'm selecting a Deal, related to company.

First solution in my mind, make notes table like this:

Note ID	Company ID	Deal ID
1	1	1
1	1	2
2	1	1
2	1	2
3	2	3
3	2	4
4	2	3
4	2	4

How i can make it?

It also seem a little bit complicated.

Is there any way to reach it with measure formula with set analysis?

[MindaugasBacius]

You can leave the data model as it is. The tables will connect automatically via field Company ID. The association will remain while selecting data in the list.

[reddy-s]

Hi,

I think you will even need a set analysis statement if you link all the three tables on Company ID. All you have to do is rename the company field in the deals table to Company ID and load the three tables.

Once done, use this measure: count([Deal ID])

Dimension : Company ID -> this would show : company ID 1 has 2 deals and Company ID 2 has 2 deals

Thanks,

Sangram.

[jonathandienst]

I agree that you should load the data as is without joining. Let QV handle the associations as it is designed to. If you join the file you will create a lot of unnecessary trouble and complexity.