Forum Discussion

rkaushik's avatar
rkaushik
Frequent Visitor
3 years ago

Matrix replicating values for all users

Hi,

I am trying to create a matrix using two tables:

Accounts Quota:

Account idUser idQuota
1110
2220

 

Credit:

Account idUser idCredit
1130
1310
2220

These tables have a one to many relationship using account id

I am getting the following matrix after applying dax measures:

Account idUser idQuotaCredit
111030
131010
222020

 

I want to show the matrix like this:

Account idUser idQuotaCredit
111030
13 10
2220

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
Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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's avatar
      rkaushik
      Frequent Visitor
      3 years ago

      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:

      accountuserquota
      3450

       

      credit table:

      account iduser idcredit
      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 iduser idquotacredit
      3450  
      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        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"