topic Re: Filter a measure based on a condition on another measure in DAX Commands and Tips

Filter a measure based on a condition on another measure

Anonymous — Mon, 18 Oct 2021 05:31:16 GMT

Hi there,

I have 2 facts based on 2 differents tables: SalesAmount and BudgetAmount.

I'd like to create a measure "SalesComparsion" which should consider "SalesAmount" only if the Customer (dimension joined to both facts), has a budget, this measure should work though I do not use the "customer" as detail in the analysis, for instance a monthly analysis.

How do I solve this?

Thank you

Re: Filter a measure based on a condition on another measure

VahidDM — Mon, 18 Oct 2021 05:33:37 GMT

Hi Anonymous

Can you add a sample of your data n a table format, and result?

Appreciate your Kudos!!

Re: Filter a measure based on a condition on another measure

Anonymous — Mon, 18 Oct 2021 05:56:52 GMT

Hi VahidDM ,

some screenshots: the relations of the 3 involved, data contained and the actual results.

Thanks

Re: Filter a measure based on a condition on another measure

VahidDM — Mon, 18 Oct 2021 06:22:58 GMT

Hi Anonymous

Try this to find the Sale Amount:

Measure = VAR _A = FILTER ( ADDCOLUMNS ( SALE, "Budget", LOOKUPVALUE ( BUDGET[AMOUNT], BUDGET[Customer_COD], SALE[Customer_COD] ) ), [Budget] > 0 ) RETURN SUMX ( _A, [AMOUNT] )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

Re: Filter a measure based on a condition on another measure

Anonymous — Mon, 18 Oct 2021 06:31:00 GMT

Great!

It's exactly what I war looking for.

Thanks