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 , 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