topic Re: Create a measure with filter customer = customer in DAX Commands and Tips

Create a measure with filter customer = customer

Anonymous — Thu, 02 Jun 2022 08:52:18 GMT

Hi all,

I am trying to create a measure with values from different tables. They all have a 'customer' column and there are relationships (1:many). Now I want to return the values from certain columns, only when Customer = Customer. If I create a filter Customer = "Customer Name 1" it works, but then I need to create a meassure for every single customer.

How can I get around this?

Thank you!

Re: Create a measure with filter customer = customer

tamerj1 — Thu, 02 Jun 2022 09:05:41 GMT

Hi Anonymous
would you please provide more details?

Re: Create a measure with filter customer = customer

Anonymous — Thu, 02 Jun 2022 10:12:20 GMT

My apologies, I didn't know how to describe the issue properly without sharing too much information about what my data looks like.

Let me try to give a simplified example:

So I have table 'Machines' and 'Average machine price in thousands' (and in practice several more) and I want to calculate the values in these tables as if there is a row context based on the Customer name. So I want to grab the value in Customer name and only multiply the values where customer names are the same, so multiply the value of Customer 1 in one table with the value of customer table in the other.
Machines

Customer name	Machines
Customer 1	5
Customer 2	3
Customer 3	4
Customer 4	1
Customer 5	5

Average machine price in thousands

Customer Name	Average amount
Customer 1	800
Customer 2	746
Customer 3	479
Customer 4	401
Customer 5	645

I want to multiply 'Machines' with Average Machine Price in Thousands' to get the total spend per customer for instance. (Apologies if it still sounds vague)

Re: Create a measure with filter customer = customer

tamerj1 — Thu, 02 Jun 2022 10:16:27 GMT

Anonymous

Thank you it's now clear. Only one thing: any relationship between the the tables? If not, can you create one? What is the cardinality? Is it 1:1?

Re: Create a measure with filter customer = customer

Anonymous — Thu, 02 Jun 2022 10:21:01 GMT

There are 1:many relationships between the tables.

Re: Create a measure with filter customer = customer

tamerj1 — Thu, 02 Jun 2022 10:22:54 GMT

Thank you Anonymous

I guess the machines table is the many?

Re: Create a measure with filter customer = customer

Anonymous — Thu, 02 Jun 2022 10:24:00 GMT

Correct.

Re: Create a measure with filter customer = customer

tamerj1 — Thu, 02 Jun 2022 10:25:45 GMT

Anonymous

Please try

Measure1 = SUMX ( Machines, Machines[Machines] * RELATED ( 'Average machine price'[Average amount] ) )