Removing columns with zero values - matrix table

[ronaldbalza2023]

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 filter on the visual however, it filters only on the rows that has total zeros in it.

Here's what inside the visualization

Here are the measures:

P&L = 
(
    CALCULATE (
        SUM ( 'Invoices'[Line Amount FX Calculation] ),
        FILTER (
            'Accounts',
            Accounts[Class] = "Revenue"
                || Accounts[Class] = "Expense"
        ), USERELATIONSHIP( Invoices[Invoice Line ID], 'Tracking Category CONNECTIONS'[ID])
    )
) - [P&L - Credit Notes] + [P&L (Journals)

P&L - Credit Notes = 
CALCULATE (
    SUM ( 'Credit Notes'[Line Amount Credit Note Calculation FX] ),
    FILTER (
        'Accounts',
        'Accounts'[Class] = "Revenue"
            || Accounts[Class] = "Expense"
    ), USERELATIONSHIP( 'Credit Notes'[Credit Note Line ID], 'Tracking Category CONNECTIONS'[ID])
)

P&L (Journals) = 
CALCULATE ( 0 - ( 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])
) 

[amitchandak · Accepted solution]

@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]

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]

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