Show a zero instead of blank in a filtered calculated table

[Anonymous]

Hi guys,

I'm using a calculated table to count the number of lines in a table and display it in my goal table with this code :

RESULTATS = SUMMARIZE(
        FILTER(
            CROSSJOIN('PRODUCTION_MAINTENANCE';'OBJECTIFS');
            --- Join criteria here
            'PRODUCTION_MAINTENANCE'[CLIENT]='OBJECTIFS'[CLIENT]
            && 'PRODUCTION_MAINTENANCE'[OPERATION]='OBJECTIFS'[OPERATION]
            && 'PRODUCTION_MAINTENANCE'[ETABLISSEMENT]='OBJECTIFS'[TECHNICENTRE]
            && 'PRODUCTION_MAINTENANCE'[DATE_SORTIE]='OBJECTIFS'[JOUR]
            );
            'OBJECTIFS'[CLIENT];
            'OBJECTIFS'[OPERATION];
            'OBJECTIFS'[TECHNICENTRE];
            'OBJECTIFS'[JOUR];
            'OBJECTIFS'[NOTIFICATION];
            'OBJECTIFS'[MODULATION];
            "PRODUCTION";COUNTROWS('PRODUCTION_MAINTENANCE')
            )

It works great BUT when i have a date in my goal table ('OBJECTIFS'[JOUR]) and no line the same day in my production table (PRODUCTION_MAINTENANCE[DATE_SORTIE]) the calculated table display nothing instead of a line with my goal and zero in my production calculated column.

Can you help me solve this ? Thanks 🙂

[mattbrice]

Maybe you can do this...

...
"PRODUCTION";COUNTROWS('PRODUCTION_MAINTENANCE') + 0
)

[Anonymous]

Thanks MATTBRICE, i've tried but it's not working.

I think that's becase my GOAL table has every 2017's days in it and my PRODUCTION table only the past 2017 days.

Any other idea ? 🙂

[Anonymous]

Hi @Anonymous,

Directly create a measure in your OBJECTIFS table using the DAX below and check if it returns your expected result.

Measure 2 = COUNTROWS(FILTER(CROSSJOIN(OBJECTIFS,PRODUCTION_MAINTENANCE), OBJECTIFS[CLIENT]=PRODUCTION_MAINTENANCE[CLIENT]&&OBJECTIFS[OPERATION]=PRODUCTION_MAINTENANCE[OPERATION]&&OBJECTIFS[TECHNICENTRE]=PRODUCTION_MAINTENANCE[ETABLISSEMENT]&&OBJECTIFS[JOUR]=PRODUCTION_MAINTENANCE[DATE_SORTIE]))+0

Thanks,
Lydia Zhang

[Anonymous]

Just tried but because my tables are quite big it seems pretty long to process... Waiting for the result to show up and coming back to you 😉

[Anonymous]

Ok so it works but it's veeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeery long to process...

Do you think i can add this DAX in a new column of my OBJECTIF table instead of a new measure. It seems that the measure process is calculated each time, so it's very long to process...

[Anonymous · Accepted solution]

Hi @Anonymous,

You can create a new column instead using the following DAX.

Column = CALCULATE(COUNTROWS(FILTER(CROSSJOIN(OBJECTIFS,PRODUCTION_MAINTENANCE), OBJECTIFS[CLIENT]=PRODUCTION_MAINTENANCE[CLIENT]&&OBJECTIFS[OPERATION]=PRODUCTION_MAINTENANCE[OPERATION]&&OBJECTIFS[TECHNICENTRE]=PRODUCTION_MAINTENANCE[ETABLISSEMENT]&&OBJECTIFS[JOUR]=PRODUCTION_MAINTENANCE[DATE_SORTIE]))+0,ALLSELECTED(OBJECTIFS[CLIENT]))

Thanks,
Lydia Zhang

[Anonymous]

Thanks i'll try it 🙂

What do you think will be the fastest to process for Power BI Online ? Is there a difference ?

[Anonymous]

Hi @Anonymous,

In your scenario, calculated column is useful than measure, and it processes faster. For the difference between calculated column and measure, please review the similar thread: https://community.powerbi.com/t5/Desktop/column-vs-measure/td-p/13201 .

Thanks,
Lydia Zhang