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

    Hey Amitchandak

     

    Still a little lost with this one......

     

    Below is a copy of my fields panel:

     

    2 x tables, a measure and a column involved

     

    The measure at the top (#Customer Balance) is the one that I am busy with but not quite there yet 😞

     

     

     

     

  • 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