Using a multiple filter

[Anonymous]

Hi everyone,

This is my first post and, first of all, hello to everyone here 🙂

I'm new to Power BI ( a few days) and i'm trying to built a Production management tool for my company.

I have a table where i have a production table with these items : SITE, CUSTOMER, PRODUCT TYPE, PRODUCT ID

And a table where i have my goals by SITE, CUSTOMER and PRODUCT TYPE.

In this last table i wan to have a calculater column that counts the number of product ID for each line of my goals table. So i try to use the COUNTROWS function in a filtered version of my production table with this expression :

PRODUCTION= COUNTROWS(FILTER(PRODUCTION;PRODUCTION[SITE]=GOALS_2017[SITE]&&PRODUCTION[PRODUCT TYPE]=GOALS_2017[PRODUCT_TYPE]&&PRODUCTION[CUSTOMER]=GOALS_2017[CUSTOMER]))

It works with one filter but when i put a second filter with the && operator it does not work anymore...

Can you help me please ? 🙂

Thanks !

[ovetteabejuela]

Can you post the sample table please?

[Anonymous]

Of course, here is my production table :

MEX	TP	SOUS SERIE	OPERATION	CODE ETAB	ENTREE	SORTIE
808797737000	9906	R39	DEP	57932	27/12/2016	02/01/2017
808797758410	9906	R39	DEPOCW	52140	29/12/2016	02/01/2017
118739387140	3425	R90	DEP	12757	30/12/2016	02/01/2017
818735389970	3425	R30	REPOCW	52140	29/12/2016	02/01/2017

And my goal table :

CODE ETAB	OPERATION	TP	OBJECTIF
57932	DEP	9906	20
52140	DEPOCW	3425	362

I would like to create a new column in my goal table with the number of MEX produced for the DEP operation by the CODE ETAB 57932 for the TP 9906 to be able to compare it to the goal (objectif).

Thanks 🙂

[Phil_Seamark]

You could try a calculated table like this...

New Table = SUMMARIZE(
        FILTER(
            CROSSJOIN('Production','Goals_2017'),
            --- Join criteria here
            'Goals_2017'[CODE ETAB] = 'Production'[CODE ETAB]
            && 'Goals_2017'[OPERATION] = 'Production'[OPERATION]
            && 'Goals_2017'[TP] = 'Production'[TP]
            ),
            'Goals_2017'[CODE ETAB],
            Goals_2017[OPERATION],
            Goals_2017[TP],
            Goals_2017[OBJECTIF],
            "Count of Mex",COUNTROWS('Production')
            )

[Anonymous]

It's working... ...but if the result of the count mex item is zero, it will show nothing.

I would like to have all the items even if the countrows result is zero, is it possible ?

[Anonymous]

Unsolved the topic...

Anyone on this ? 🙂