Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DeonDP
Helper II
Helper II

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 - any help would be appreciated.

 

Sample below - there are debtors with negative balances as well.

 

DeonDP_0-1642572250531.png

 

Cheers

 

D

 

10 REPLIES 10
DeonDP
Helper II
Helper II

Thanks for your quick reply Amitchandak - will check it out and reply real soon.

amitchandak
Super User
Super User

@DeonDP , 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])))

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

@DeonDP , 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])

 

 

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])
 
DeonDP_0-1642583443080.png

 

 

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])))
DeonDP_1-1642583464699.png

 

I really appreiate your help to this point.

 

cheers

 

D

 

Hi, @DeonDP 

 

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.

vjaneygmsft_0-1643018366879.png

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

 

Thank you so much for your reply Janey 

 

I will get some data together & upload just so we can get to the bottom of all of this

 

Thnaks again

 

Deon 

BTW - I totally hashed the measure that I provided, so no doubt you would have been confused 🙂

 

Apologies for that

@DeonDP 

 

Because your formula involves different tables and is used in a matrix (complex context) visual, so just sharing measure is not enough to modify, we need data to analyze. Of course, it would be great if you could follow my advice and modify it yourself.

 

Best Regards,
Community Support Team _ Janey

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 😞

 

DeonDP_0-1642575084245.png

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.