Forum Discussion
Replacing blank values with zero
ronaldbalza2023 You're converting the 0s to blanks with the P&L (no blanks) measure. Are you saying you want that back to 0? Can you simply use the P&L measure?
The problem is, that you have a matrix so it will replace ALL rows with zero - how do you specify which rows you want to show or not?
You may be able to work with an IF() statement, but we need to know which columns you're using in that matrix visual please and you could use that context to filter out rows where the total row is blank, otherwise put 0 - is that what you mean?
ronaldbalza2023 Sorry, I was trying with a simplified version of [P&L] but you're right it doesn't work in your measures.
Try updating the [P&L (No Blanks)] measure and use that in your visual instead:
P&L (No Blanks) =VAR _ColSubTotal = CALCULATE([P&L], ALL(DimAccount[Account Type), ALL(DimAccount[Name]))VAR _Result =IF(_ColSubTotal <> 0, [P&L])RETURN _ResultI don't know what table the Account Type and Name are coming from, so you may need to update that part of the measure.ronaldbalza2023 I thought your [P&L] measure already had the $0 included in it? So the $0 should display if you've got the ColSubtotal part correct....
You could try adding the zero back in:
P&L (No Blanks) =VAR _ColSubTotal = CALCULATE([P&L], ALL(DimAccount[Account Type), ALL(DimAccount[Name]))VAR _Result =IF(_ColSubTotal <> 0, [P&L]+0)RETURN _ResultThat's because they are blank for that account type. Do you have a DimAccount table?
Can try replacing the ALL filters with just
ALL(DimAccount)
Hi AllisonKennedy , hooray! after a gruelling nights spending time with this 🙂 Thanks so much for your help.
Hi ronaldbalza2023, can you remove the "Calculate" and just leave the IF...
TheoC still doing the same. Nothing really happened.