I can't get a measure to show a value based on another measure's value

[wpf_]

I have a table that show which employees are working today.  And if the employee is out, it should show the replacement's name and job number.  However I am not able to show the replacement's job number now.  

I need some help. 

Here is my sample pbix file. Thanks.

https://drive.google.com/file/d/1qOXRswGZD6u-ZBAphHOT16XDc9iVqUyK/view?usp=sharing

[v-henryk-mstf]

Hi,
According to your requirements, I recreated a measure on the Dynamic Replacement you created. The judgment condition is to return the Job Number of the current row when the columns are the same, otherwise return the value of the Job Number corresponding to the Employee Name. The following results can be used as a reference:

Measure = 
var M_Cur_Name = [Dynamic Replacement]
 
var cur_job_Number = MAX(Employee[Job Number])
 
var job_number = CALCULATE(MAX(Employee[Job Number]), FILTER(ALL(Employee), Employee[Employee Name]= M_Cur_Name))
 
return
IF(M_Cur_Name = MAX(Employee[Employee Name]),cur_job_Number,job_number)

Here is the sample pbix file.

If the problem is still not resolved, please provide detailed error information and test data. Let me know immediately, looking forward to your reply.

Best Regards,
Henry

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

[wpf_]

@v-henryk-mstf Thanks. I was able to get what I wanted.  I am having a better understanding now. And thanks for your example. 

[v-henryk-mstf · Accepted solution]

Hi @wpf_ ,

According to your requirements, I recreated a measure on the Dynamic Replacement you created. The judgment condition is to return the Job Number of the current row when the columns are the same, otherwise return the value of the Job Number corresponding to the Employee Name. The following results can be used as a reference:

Measure = 
var M_Cur_Name = [Dynamic Replacement]
 
var cur_job_Number = MAX(Employee[Job Number])
 
var job_number = CALCULATE(MAX(Employee[Job Number]), FILTER(ALL(Employee), Employee[Employee Name]= M_Cur_Name))
 
return
IF(M_Cur_Name = MAX(Employee[Employee Name]),cur_job_Number,job_number)

Here is the sample pbix file.

If the problem is still not resolved, please provide detailed error information and test data. Let me know immediately, looking forward to your reply.

Best Regards,
Henry

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

[wpf_]

I have two tables:  employee and replacement.  in the table visual i have the employee job position in one column, then i have a measure that gives me the respective empoyee name tied to that job position.  The employee name changes to his replacement if the employee is out for that day.  Now I want a third column that shows the employee's ID but based on which employee is in (based on that measure).  How do you write a measure or calculated column based on which employee is listed for each row from the measure?

Here is the table structure:

employee: job position, employee name, employee ID

replacement: measure (to determine who is out, and who the replacement is).  

Thanks 

[parry2k]

@wpf_ can you simply paste a sample data and expected output, or throw something in excel and share it thru one drive/google drive. 

[wpf_]

@parry2k Here is the pbix file. Thank you so much for helping.  So i basically want to show the job number for who ever is in the replacements column.   Here is the pbix file: https://drive.google.com/file/d/1qOXRswGZD6u-ZBAphHOT16XDc9iVqUyK/view?usp=sharing

[parry2k]

@wpf_ could you share sample data and expected output.

[wpf_]

@parry2k Hi here is an illustration.

[v-henryk-mstf]

Hi @wpf_ ,

According to your requirements, I recreated a measure on the Dynamic Replacement you created. The judgment condition is to return the Job Number of the current row when the columns are the same, otherwise return the value of the Job Number corresponding to the Employee Name. The following results can be used as a reference:

Measure = 
var M_Cur_Name = [Dynamic Replacement]
 
var cur_job_Number = MAX(Employee[Job Number])
 
var job_number = CALCULATE(MAX(Employee[Job Number]), FILTER(ALL(Employee), Employee[Employee Name]= M_Cur_Name))
 
return
IF(M_Cur_Name = MAX(Employee[Employee Name]),cur_job_Number,job_number)

Here is the sample pbix file for reference.

If the problem is still not resolved, please provide detailed error information and let me know immediately, looking forward to your reply.

Best Regards,
Henry

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

[pranit828]

Hi @wpf_ 

If Jack replaced Lisa, won't lisa job number be assigned to Jack, i.e., 22222 and you would just need to use the 2nd column from your screenshot.

[wpf_]

@pranit828 @The job number stays with the employees.  Lisa will keep 2222 and jack will keep 7777.  I just want to show that if Lisa is out jack will take his place and his job number is 7777

[pranit828]

HI @wpf_ 

So, Jack will have same job number eventhough he is tied to different Location (B and G)

[wpf_]

@pranit828 @Yes jack can work in two locations.  Job number follows him