How to filter a measure by measure

[herbemischung]

I don't know if this is possible (maybe its easy and I just don't get it)

In Table A there is "Sales Amount" - I created 2 Measures:

AJ = Sales this year (Totalydt(sum(Table A[Sales Amount]);Table A[Date].[Date])

VJ = Sales the year before (Calculate(AJ;Previousyear(Table A[Date].[Date]

In Table B there is "Customer"

In Table A there also is "Article"

I aim to get a list of articles that where bought by those customers that have not (yet) bought anything in the acutal year.

VJ of those Customers that have AJ = 0

I thought of thomething like: VJ_filtered =  Calculate([VJ];where (Table B[Customer]; [AJ] =0))

--> i know that this is no formula at all, but I think it makes clear what I want to achieve.

Further on I would make a List with "Article" and VJ_filtered

Is there a Formula where I can do this? Thank You 🙂

[theov]

I'd recommend you wathing this for measure filters, with some work around you cand get your case fixed also:
https://www.youtube.com/watch?v=Ss5L7jXIhso

[v-yuta-msft]

Hi herbemischung,

What's the relationship between table A and table B? You should create a relationship between table A and table B based on some columns.

For example column [Article] is the basic column, convert AJ to a calculate column, then you can use DAX formual based on Table B below and try again.

VJ_filtered =  sumx(Calculatetable(Table B,  [AJ] = 0)), Table B[Customer]

Regards,

Jimmy Tao

[herbemischung]

I somehow don't get it done.

It seems to be so simple, but maybe it isn't

in the meantime I have all relevant fields in one table. 

[Floriankx]

Hello maybe Calculate is suitable:

CALCULATE([VJ];Filter(Sales;[AJ]=0))

[herbemischung]

Thank you, but I am afraid this would just give me the Sales Amount 2017 for all articles with no Sales in 2018.

But I need the Sales Amount 2017 for all articles bought by Customers with no Sales in 2018 😕

[Floriankx]

Okay then maybe something different:

SUMX(Customer;If[AJ]=0;[VJ];0)

I haven't built a trial sheet yet. So it's blind guessing.