topic Re: Measure based on measure in other table in DAX Commands and Tips

Measure based on measure in other table

livios — Tue, 28 Jan 2020 16:30:00 GMT

Hi,

I have 2 tables with these columns/measures (there is a one to many relationship between accounts and salesorders):

accounts

- column 'estimated_sales' (estimated sales value)

salesorders

- measure 'total_sales_this_year' = sum(salesorders[amount])

- column 'amount' (sales amount for this order)

I want to create this overview table and I only want to show accounts which have estimated_sales > 0 or total_sales_this_year > 0:

account name - estimatedsales - totalsalesthisyear

Since you can't filter on 2 values with an 'or' relation I want to create a boolean measure 'has_sales_this_year ' in the account table so I can filter on this value but I can't figure out the correct syntax ?

has_sales_this_year = [estimated_sales] > 0 or salesorder[total_sales_this_year] > 0

Re: Measure based on measure in other table

avanderschilden — Tue, 28 Jan 2020 19:48:57 GMT

Hi livios,

Please check the attachment and let me know;

https://we.tl/t-8QELBXoolB

Regards,

Adrian

Re: Measure based on measure in other table

livios — Wed, 29 Jan 2020 08:40:59 GMT

Hi,

Thx for the example but this is not what I need exactly. I need a boolean measure on the accounts table which indicates if accounts.estimated_sales > 0 or sales_orders.total_amount > 0.

The sales_orders table also has an intake date and I need to filter the total sales per account on year and/or month and the total_amount and boolean measure need to reflect the intake date filter.

Re: Measure based on measure in other table

Anonymous — Wed, 29 Jan 2020 08:48:01 GMT

Can you share your sample data and expected output.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Re: Measure based on measure in other table

livios — Wed, 29 Jan 2020 11:18:49 GMT

Hi,

I changed the example to explain what I want. In my example I want to be able to filter the "Total sales this year" grid so account C is not displayed because this account has no salesorders in 2019 and the estimated sales for this account is also 0.

https://we.tl/t-ZlS9x9L6sA

Re: Measure based on measure in other table

livios — Fri, 07 Feb 2020 09:48:21 GMT

Hi,

Looks that I don't need the measure since records with no "Total sales this year" and no "Total sales last year" and no "Estimated sales" are not displayed in the table.