finding linked accounts in different tables

[leela77]

Hello. 
I want to write a measure (without the use of creating calculated columns) that gives me the number of opportunities with linked accounts. 

There are three tables:

1. fact_pipeline_events

2. dim_account

3. dim_pipeline_event

All opportunities need to be identified via 

fact_pipeline_events table[total_opportunity_created_events] = 1,

dim_pipeline_event[pipeline_object] = "opportunity"

Once the opportunity has been identified, it needs to look up that particular account_number from fact_pipeline_events in the dim_account[account_number].

Now here comes the bit I am struggling with:

The dim_account is temporarily filtered by dim_account[account_number] in order to find a value in another column: dim_account[duns_number].
The whole dim_account table then needs to be filtered by dim_account[duns_number], i.e. removing the temporary filter in dim_account[account_number]. If dim_account then shows up with more than one account_number, these are the linked accounts, that I need to count.

How can I accomplish this, please?

Thank you very much in advance. 

[Anonymous]

Hi, @leela77 

Perhaps you can refer to the following DAX

LinkedOpportunitiesCount = 
VAR OpportunityAccounts = 
    FILTER(
        'fact_pipeline_events',
        [total_opportunity_created_events] = 1
        && RELATED(dim_pipeline_event[pipeline_object]) = "opportunity"
    )
VAR AccountsWithDUNS = 
    CALCULATETABLE(
        'dim_account',
        FILTER(
            'dim_account',
            [account_number] IN VALUES(OpportunityAccounts[account_number])
        )
    )
VAR DUNSLinkedAccounts = 
    SUMMARIZE(
        AccountsWithDUNS,
        [duns_number],
        "LinkedAccounts", DISTINCTCOUNT([account_number])
    )
RETURN
    SUMX(DUNSLinkedAccounts, [LinkedAccounts])

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[leela77]

Hello Yongka Hua, 
Thank you very much for your quick reply. I'm afraid this doesn't work as expected. OpportuntiyAccounts[account_name] isn't a table with an account_name column. It is a variable which you have created. 
Furthermore, I also need to check whether - once the dim_account table has been filtered to that particular duns_number - it has two accounts, which means it is linked. I am thinking of an IF statement here along the lines IF(DISTINCTCOUNT(dim_account[account_name]) = 2