Group by with filter

[Vanessa250919]

Hello, I created a group by table in dax, it's possible to add a filter  ? I need for all average to filter 0 vallue  . 

Thanks 

pivot =

GROUPBY(

MASTERTABLE,MASTERTABLE[POSTAL_CODE],MASTERTABLE[DIENST], MASTERTABLE[Key],

"PAK_VOL_REEL",AVERAGEx(CURRENTGROUP(),MASTERTABLE[PAK_VOLUME_REEL]),

"PAK_VOL_ORG",AVERAGEX(CURRENTGROUP(),MASTERTABLE[PAK_VOLUME_ORG]),

"EE_VOL_ORG",AVERAGEX(CURRENTGROUP(),MASTERTABLE[EE_INDIV_ORG]),

"EE_VOL_REEL",AVERAGEX(CURRENTGROUP(),MASTERTABLE[EE_INDIV_REEL]),

"ENA_VOL_ORG",AVERAGEX(CURRENTGROUP(),MASTERTABLE[ENA_VOLUME_ORG]),

"ENA_VOL_REEL",AVERAGEX(CURRENTGROUP(),MASTERTABLE[ENA_VOLUME_REEL]),

"FNGF_VOL_ORG",AVERAGEX(CURRENTGROUP(),MASTERTABLE[FNGF_VOLUME_ORG]),

"FNGF_VOL_REEL",AVERAGEX(CURRENTGROUP(),MASTERTABLE[FNGF_VOLUME_REEL]),

"KRANT_VOL_ORG",AVERAGEX(CURRENTGROUP(),MASTERTABLE[KRANT_VOLUME_ORG]),

"KRANT_VOL_REEL",AVERAGEX(CURRENTGROUP(),MASTERTABLE[KRANT_VOLUME_REEL])

[selimovd · Accepted solution]

Hey @Vanessa250919 ,

sure, you can use the FILTER function to filter the group by table:

FILTER
    GROUPBY (
        MASTERTABLE,
        MASTERTABLE[POSTAL_CODE],MASTERTABLE[DIENST], MASTERTABLE[Key],
        "PAK_VOL_REEL",AVERAGEx(CURRENTGROUP(),MASTERTABLE[PAK_VOLUME_REEL]),
        "PAK_VOL_ORG",AVERAGEX(CURRENTGROUP(),MASTERTABLE[PAK_VOLUME_ORG]),
        "EE_VOL_ORG",AVERAGEX(CURRENTGROUP(),MASTERTABLE[EE_INDIV_ORG]),
        "EE_VOL_REEL",AVERAGEX(CURRENTGROUP(),MASTERTABLE[EE_INDIV_REEL]),
        "ENA_VOL_ORG",AVERAGEX(CURRENTGROUP(),MASTERTABLE[ENA_VOLUME_ORG]),
        "ENA_VOL_REEL",AVERAGEX(CURRENTGROUP(),MASTERTABLE[ENA_VOLUME_REEL]),
        "FNGF_VOL_ORG",AVERAGEX(CURRENTGROUP(),MASTERTABLE[FNGF_VOLUME_ORG]),
        "FNGF_VOL_REEL",AVERAGEX(CURRENTGROUP(),MASTERTABLE[FNGF_VOLUME_REEL]),
        "KRANT_VOL_ORG",AVERAGEX(CURRENTGROUP(),MASTERTABLE[KRANT_VOLUME_ORG]),
        "KRANT_VOL_REEL",AVERAGEX(CURRENTGROUP(),MASTERTABLE[KRANT_VOLUME_REEL])
    ),
    [ENA_VOL_REEL] > 0 && [KRANT_VOL_REEL] > 0
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Vanessa250919]

Hello @selimovd , I tryed to make a filter like this :

pivot = FILTER(

GROUPBY (

MASTERTABLE,

MASTERTABLE[POSTAL_CODE],MASTERTABLE[DIENST], MASTERTABLE[Key],

"PAK_VOL_REEL",AVERAGEx(CURRENTGROUP(),MASTERTABLE[PAK_VOLUME_REEL]),

"PAK_VOL_ORG",AVERAGEX(CURRENTGROUP(),MASTERTABLE[PAK_VOLUME_ORG]),

"EE_VOL_ORG",AVERAGEX(CURRENTGROUP(),MASTERTABLE[EE_INDIV_ORG]),

"EE_VOL_REEL",AVERAGEX(CURRENTGROUP(),MASTERTABLE[EE_INDIV_REEL]),

"ENA_VOL_ORG",AVERAGEX(CURRENTGROUP(),MASTERTABLE[ENA_VOLUME_ORG]),

"ENA_VOL_REEL",AVERAGEX(CURRENTGROUP(),MASTERTABLE[ENA_VOLUME_REEL]),

"FNGF_VOL_ORG",AVERAGEX(CURRENTGROUP(),MASTERTABLE[FNGF_VOLUME_ORG]),

"FNGF_VOL_REEL",AVERAGEX(CURRENTGROUP(),MASTERTABLE[FNGF_VOLUME_REEL]),

"KRANT_VOL_ORG",AVERAGEX(CURRENTGROUP(),MASTERTABLE[KRANT_VOLUME_ORG]),

"KRANT_VOL_REEL",AVERAGEX(CURRENTGROUP(),MASTERTABLE[KRANT_VOLUME_REEL])

) ,[PAK_VOL_ORG] >0 || [KRANT_VOL_ORG] >0 || [EE_VOL_ORG] >0 || [ENA_VOL_ORG] >0 || [FNGF_VOL_ORG]>0 )

But I have always a 0 values 

[selimovd]

Hey @Vanessa250919 ,

in your measure you didn't filter for [PAK_VOL_REEL] > 0, so it will also show the 0 values for that Group by column.

I can't tell you why the values are 0 as you didn't share any data with us.

Does that make sense?

Best regards

Denis