Is it possible to integrate the constant DAX expression into Power BI

[denxx34]

Hello, everyone,

I have the following request.

I have sales (Sales Invoice Line) per salesperson.

Salesperson has specific goals (Target). Such as Amount in a specific city or Amount to a specific customer.

I would like to maintain the targets for each salesperson in the target table, including the DAX expression.

Is it possible to integrate the constant DAX expression from Excel (Column "DAX Filter" in "Target" table) into Power BI?

So that I don't have to create a measure per city, but only one measure "Amount City"

Something like this:

New Measure:

Amount City = [DAX Filter]

My data model:

Output:

Sample Data:

Target

Code	Type	Plan	DAX Filter	Planned	Year
JR	Amount with Customer + DAX	Amount in City	CALCULATE( [Sum of Amount] , FILTER('Sales Invoice Line' , 'Sales Invoice Line'[City] = "München" ) )	10000	2010
AH	Amount with Customer + DAX	Amount in City	CALCULATE( [Sum of Amount] , FILTER('Sales Invoice Line' , 'Sales Invoice Line'[City] = "Frankfurt a. M." ) )	200	2010
PS	Amount with Customer + DAX	Amount with Customer	CALCULATE( [Sum of Amount] , FILTER('Sales Invoice Line' , 'Sales Invoice Line'[Sell-to Customer No_] = "40000" ) )	1500	2010
AH	Count of	Count of Order in Suisse		25	2010
JR	Count of	Count of Order in Austria		20	2010
JR	Count of	Count of Customers in Austria		50	2010

Sales Invoice Line

Document No_	Line No_	Sell-to Customer No_	No_	Amount	Quantity	Salesperson Code	Posting Date	City
103019	10000	40000	8908-W	530,4	3	PS	13.12.2010	Berlin
103019	20000	40000	8916-W	579,6	2	PS	13.12.2010	Berlin
103019	30000	40000	8924-W	536,3	1	PS	13.12.2010	Berlin
VRG1001168	40000	113000	1996-S	2780,46	2	JR	07.06.2010	München
VRG1001328	20000	113000	1928-W	1049,18	2	JR	28.06.2010	München
VRG1000746	20000	113000	1960-S	383,5	2	JR	12.04.2010	München
VRG1001382	10000	113000	1928-W	1049,18	2	JR	05.07.2010	München
VRG1001434	70000	113000	1984-W	1494,78	1	JR	12.07.2010	München
VRG1001434	60000	113000	1980-S	189,11	1	JR	12.07.2010	München
VRG1002255	30000	129000	1968-W	1494,78	1	JR	01.11.2010	München
VRG1002255	20000	129000	1960-S	191,75	1	JR	01.11.2010	München
VRG1002497	20000	129000	1928-S	54,52	1	JR	06.12.2010	München
VRG1002255	10000	129000	1896-S	995,74	1	JR	01.11.2010	München
VRG1002497	10000	129000	1908-S	189,11	1	JR	06.12.2010	München
VRG1002498	10000	127000	1908-S	234	3	GD	06.12.2009	Stuttgart
VRG1008498	10000	127000	1908-W	289,13	2	VR	06.11.2009	Stuttgart
VRG1002454	10000	125000	1908-S	234	3	AH	06.12.2010	München
VRG1001455	10000	114000	1980-S	169,11	1	JR	12.07.2010	Heilbronn
VRG1002219	10000	12200	1968-W	1794,78	1	JR	01.11.2010	Heilbronn
VRG1002875	10000	129000	1908-S	289	4	AH	06.10.2010	Frankfurt a. M.

I thank you in advance!

[v-janeyg-msft]

@denxx34  Hello

Any updates? Do you still need help?

[v-janeyg-msft]

Hi, @denxx34 

According to the data you have provided, If there are not many types of cities, it is recommended to write measures separately, because your data is more complex.

E.g: I see that a sales person in your data may sell more than one city, how to deal with this situation?

Best Regards,

Community Support Team _Janey

[amitchandak]

@denxx34 , You need to have target table, salesperson, city and date(month/year etc)  and that need join with column tables like salesperson, city, and date. same is true sales invoice table

[denxx34]

Thanks for your answer, but I don't quite understand what you mean. Can you please explain it in a little more detail?