How to Group by filter ONLY VALUES SUM(MBR_MOS) Greater than 0

[NilR]

Hi,

In my below group by how I can add filter that if the sum or MBR_MOS column is not 1 or more then do not include.

VAR _CNSR_ID = GROUPBY(FILTER('tbl', [GP]=_gp && [Date] >= 022022 && [Date] <= 032022 && MAX('tbl'[MALES_UNDER_35]) = "Yes" && MAX('tbl'[last_22_months]) >= 22), [CNSR_ID] )

I tried  MAX([MBR_MOS]) >0 and SUM([MBR_MOS]) >0  in the filter section which they don't work. I need this group by for my measure. 

GP	CNSR_ID	DATE	MBR_MOS
321	4446	202202	0
321	4446	202201	1
321	4446	202112	1

[v-binbinyu-msft · Accepted solution]

Hi @NilR ,

Please try below steps:

1. my test table

Table:

2. create a  measure and add it to table visual

Measure =
VAR tmp =
    SUMMARIZE ( 'Table', 'Table'[ID], "Sum for Count", SUM ( 'Table'[Count] ) )
VAR tmp1 =
    FILTER ( tmp, [Sum for Count] > 0 )
RETURN
    CALCULATE ( SUM ( 'Table'[Count] ), tmp1 )

Please refer my attached .pbix file

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

You need to apply that filter separately, on the outside of the GROUPBY. It will be better to use SUMMARIZE so you can add the aggregations to the groups.