Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Filter Matrix table to exclude zero balances

Hey Guys   I created this Matrix table that shows outstanding debtor balances as an aged analysis.    I don't want to show debtors with a zero balance.    Been struggling to get it right in DAX...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous , Create a measure like this and use that in the visual level filter. Check <>  0

 

measure = calculate([meausre], filter(allseleceted(Table), Table[Brand] = max(Table[Brand]) && Table[Customer No] = max(Table[Customer No])))

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Just FYI

 

Detail of the #Receivable per group Mixed measure:

 

#Receivables Per Group Mixed =
CALCULATE('Detail Customer Ledger'[#Invoice Values],
FILTER('Detail Customer Ledger',
COUNTROWS(
FILTER('Aged Debtor Groups',
'Detail Customer Ledger'[#Days Overdue Mixed] >= 'Aged Debtor Groups'[Min] &&
'Detail Customer Ledger'[#Days Overdue Mixed] <= 'Aged Debtor Groups'[Max] ) ) > 0 ) )
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , if you want to filter a measure in a measure the you need values for one visual group  by or summarize for more than one

     

    example measure =
    Var _1 = calculate(COUNTROWS(
    FILTER('Aged Debtor Groups',
    'Detail Customer Ledger'[#Days Overdue Mixed] >= 'Aged Debtor Groups'[Min] &&
    'Detail Customer Ledger'[#Days Overdue Mixed] <= 'Aged Debtor Groups'[Max] ) ))
    return
    Sumx(filter( ADDCOLUMNS (Summarize ( 'Aged Debtor Groups'' , 'Aged Debtor Groups'[Brand] , 'Aged Debtor Groups''[Customer No]) ,
    "_measure", _1 ) , _measure >0), [#Invoice Values])

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hey Amitchandak

       

      This is what I came up with, and probably totally hashed it.

       

      I think I am missing something here.  The 'Aged Debtor Groups' table does not include any Debtor names.  That is contained in the 'DimCustomer' table.

       

      So I am not sure about this part of the measure you gave:
       
      Sumx(filter( ADDCOLUMNS (Summarize ( 'Aged Debtor Groups'' , 'Aged Debtor Groups'[Brand] , 'Aged Debtor Groups''[Customer No]) ,
      "_measure", _1 ) , _measure >0), [#Invoice Values])
       

       

       

      So this is the measure I created but totally hashed it 😞 wanting to include the Dim Customer table in some way.  I am also not clear on where the BrandCustNo&Name with zero balances are being eliminated.

       

      #Receivables Per Group Mixed NO ZERO =
      CALCULATE('Detail Customer Ledger'[#Invoice Values],
      FILTER('Detail Customer Ledger',
      Var _1 = CALCULATE(COUNTROWS(
      FILTER('Aged Debtor Groups',
      'Detail Customer Ledger'[#Days Overdue Mixed] >= 'Aged Debtor Groups'[Min] &&
      'Detail Customer Ledger'[#Days Overdue Mixed] <= 'Aged Debtor Groups'[Max] ) ))
      return
      Sumx(FILTER(ADDCOLUMNS (Summarize ( 'DimCustomer', 'DimCustomer'[BrandCustNoName]),
      "_measure", _1 ) , _measure > 0), [#Invoice Values])))

       

      I really appreiate your help to this point.

       

      cheers

       

      D

       

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi, Anonymous 

         

        According to your needs, in theory, you only need to make your measure on 'values' not display 0 but display blank(), then empty rows should not be displayed automatically.

        We don't know your data, and it is difficult to understand your situation based on the measures you provide.

        If you can share some insensitive data and expected output to help us clarify your scenario, you can help you soon.

         

        Best Regards,
        Community Support Team _ Janey