Matrix replicating values for all users

[rkaushik]

Hi,

I am trying to create a matrix using two tables:

Accounts Quota:

Account id	User id	Quota
1	1	10
2	2	20

Credit:

Account id	User id	Credit
1	1	30
1	3	10
2	2	20

These tables have a one to many relationship using account id

I am getting the following matrix after applying dax measures:

Account id	User id	Quota	Credit
1	1	10	30
1	3	10	10
2	2	20	20

I want to show the matrix like this:

Account id	User id	Quota	Credit
1	1	10	30
1	3		10
2	2	20	20

As you can see, user 3 doesn't have a quota for account 1 but is still showing quota value. The quota is replicating value for all users in an account even if the user doesn't have one. How can I show the result to be like my result table?

Here's the dax measure I am using for quota:

Quota value =

CALCULATE(

    SUM('Account Quota'[quota])

)

I am guessing I need to make modifications to the quota measure so that it displays quota only for the matching user value and show blanks for the ones which don't have a user value in accounts quota table.

Please let me know if I need to provide more information

[Greg_Deckler]

@rkaushik See attached PBIX below signature.

Quota Measure = 
    VAR __Account = MAX('Credit'[Account id])
    VAR __User = MAX('Credit'[User id])
    VAR __Result = MAXX(FILTER('Accounts Quota', [Account id] = __Account && [User id] = __User), [Quota])
RETURN
    __Result

[rkaushik]

Hey @Greg_Deckler thanks for your help.
This did help me but I noticed another issue. If in credits table, the account exists but user doesn't exist then it is not showing the account altogether. For example, 

accounts quota table:

account	user	quota
3	4	50

credit table:

account id	user id	credit
3

Then I am not seeing account 3 at all in my result matrix. Would you happen to know how I can fix that? I would still like to see:

account id	user id	quota	credit
3	4	50

[Greg_Deckler]

@rkaushik I recommend that you merge your 2 tables in Power Query like this:

let
    Source = Table.NestedJoin(#"Accounts Quota", {"Account id"}, Credit, {"Account id"}, "Credit", JoinKind.LeftOuter),
    #"Expanded Credit" = Table.ExpandTableColumn(Source, "Credit", {"Credit"}, {"Credit.Credit"})
in
    #"Expanded Credit"