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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Show a zero instead of blank in a filtered calculated table

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 🙂

1 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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
mattbrice
Solution Sage
Solution Sage

Maybe you can do this...

 

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

 

Anonymous
Not applicable

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 ? 🙂

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


1.PNG

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

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

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks i'll try it 🙂

 

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

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.