dynamic text as a measure

[Anonymous]

I am trying to create a measure, that changes values dynamically based on what is selected in the chart.  I have 2 tables called Area and Employees.

Employees:

ID  Cost _Center ....

and Area:

Cost_center  Region  Country PSL  Sub-PSL ...

The two tables are connected by the cost center. I am trying to create a title measure. When we click on a particular PSL on the chart, the text shows the following dynamically:  " The psl <Name of PSL> has <# of employees whose maximum contributers were from <Region name" with <Employee number>. I have written the following code for this:

This however, gives the psl name and number of employees for that PSL correctly, but the remaining is not shown.

1. Name of region does not appear

2. Number of employees is the same value as the one that shows for PSL

I am aware that it is because of the look up value, but not sure how to correct this

Any suggestions?

[MFelix]

Hi @Anonymous,

Why are you returning the MAX(Employe [Per Nr] on your lookup value? What do you want to return on this part of the lookup? 

If both tables are connect by Cost center you should not need to make a lookup, just the return part of the lookup.

Can you share some sample data and expected result with that sample?

Regards.

MFelix

[Anonymous]

Hey, @MFelix

So the sample PB File and Excel File are in the link below:

Excel :  https://drive.google.com/open?id=1PHEzLIOn13X2NwNT8VWiAUGS-VWzJq2Z

Power BI : https://drive.google.com/open?id=1bSyqUgMhMa4bpozHEYT_WC1uWJp4RJqa

So the power Bi file has the 2 sheets loaded with the required relationship. Basically, I will have a bar chart with PSL in X axis and Count of employees on the Y, and another bar chart with Region in X axis and Count of employees in Y.

So when i click on a particular PSL in that chart, the text measure should give me the sentence I want, for which the formula I tried is given. My text measure says "For <Psl name> (which is the name of psl selected from the bar chart), the count of employees is ________ and the maximum contribution for this was from region <Region name> with _____ Employees". 

Basically, I have a count of employees in a given psl and I want the users to know that the max contribution of these employees are from which region and the number of employees. So probably if the user selects PSL Man and there are 4 employees there and if most of those people are from region X (say 2 is that number which is maximum), then it says For PSL Man, the count of employees is 2 and the maximum contribution for this was from region X with 2 employees.

Hope I was able to ecplain this clearly! The file is a sample structure, so feel free to change it! Thank You