How to handle filter-aggregate-filter scenario?

[FreemanZ]

For the table below, I am trying to get a list of Product with 0 SalesAmount in the last 3 months. 

Month	Product	SalesAmount
Jan	A	0
Jan	B	84
Jan	C	0
Feb	A	68
Feb	B	0
Feb	C	0
Mar	A	92
Mar	B	0
Mar	C	0
Apr	A	61
Apr	B	76
Apr	C	0

How could it be done?

 

(BTW, filter-aggregate-filter is my personal understanding only, anyway workable would be great. )

[Anonymous · Accepted solution]

Hi @FreemanZ ,

Min sales:

easure = 
var minsales = CALCULATE(MIN('Table'[SalesAmount]),FILTER(ALLEXCEPT('Table','Table'[Product]),'Table'[MonthNo]>MAXX(ALLSELECTED('Table'),'Table'[MonthNo])-3))
return
IF(minsales>0,0,1)

Sum sales:

Measure = 
var sumsales = CALCULATE(SUM('Table'[SalesAmount]),FILTER(ALLEXCEPT('Table','Table'[Product]),'Table'[MonthNo]>MAXX(ALLSELECTED('Table'),'Table'[MonthNo])-3))
return
IF(minsales>0,0,1)

Best Regards,

Jay

[FreemanZ]

This code works:

EVALUATE

VAR TABLE1 =

    CALCULATETABLE (

        ADDCOLUMNS (

            SUMMARIZE ( 'Table', 'Table'[Product] ),

            "Amount", CALCULATE ( SUM ( 'Table'[SalesAmount] ) )

        ),

        FILTER ( 'Table', 'Table'[Month] IN { "Apr", "Mar", "Feb" } )

    )

VAR TABLE2 =

    FILTER ( TABLE1, [Amount] = 0 )

RETURN

    TABLE2

 

Previously I was obsessed why the more intuitive expression below doesn't work:

EVALUATE

VAR TABLE1= FILTER ('Table', 'Table'[Month] in {"Apr", "Mar", "Feb"})

VAR TABLE2 =

    SUMMARIZE( 'Table1', 'Table'[Product])

VAR TABLE3 =

    ADDCOLUMNS( TABLE2, "Amount", CALCULATE( SUM ('Table'[SalesAmount])))

VAR TABLE4 =

    FILTER (Table3, [Amount]=0)

RETURN Table4

 

It seems the first FILTER has no memory.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.