Forum Discussion

ronaldbalza2023's avatar
ronaldbalza2023
Continued Contributor
4 years ago
Solved

Removing columns with zero values - matrix table

Hi everyone, having a hard time trying to figure out this one. Appreciate  the help on this! Question : How can I remove the columns that are having zero values in Matrix table?  I have filte...
Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    ronaldbalza2023 , change this one

     

    P&L (Journals) = 
CALCULATE ( -1* ( SUM ( 'Journals'[Net Amount FX] ) ),
    FILTER ( 'Journals', 'Journals'[Split] = "JOURNALS" ),
    FILTER (
        'Accounts',
        'Accounts'[Class] = "Revenue"
            || Accounts[Class] = "Expense"
    ), USERELATIONSHIP( Journals[ID], 'Tracking Category CONNECTIONS'[ID])
)

     

     

    Also on top of you measures you can create measures like

     

    P&L (Journals) new= if([P&L (Journals)] =0, blank(), [P&L (Journals)])

ronaldbalza2023's avatar
ronaldbalza2023
Continued Contributor
4 years ago

Hi amitchandak works like a charm, thanks! However, the zeroes that has a NIL value on rows have gone. How can I replace the blank rows with zero?

 

amitchandak's avatar
amitchandak
Super User
4 years ago

ronaldbalza2023 , I doubt you can have both.

For that we need force, Like in this example I added +0, all dimension values will come and then I forced the date range again

 

0 between range
Measure = var _1= SUM(Opportunity[Opportunity count]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))