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 id User id Quota 1 1 10 2 2 20   Credit: Account id User id Credit 1 1 30 1 3 10 ...
Need Help
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"