How to filter ID;s based on another column

[Anonymous]

Hi, I have a table where there is ID, previoustasks_ttaskids and employee_ttaskresourceid columns. I need to filter the table to contain only the ID's where previoustask_ttaskid = id and display the employee_ttaskresourceid. Could someone please help on this.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Column = LOOKUPVALUE('refResourceTask (2)'[employee_ttaskresourceid],'refResourceTask (2)'[id],'refResourceTask (2)'[previoustasks_ttaskids])

Hope this helps.

[Seanan]

Hi @Anonymous 

I've created a measure to achieve this.

Matching Id's = COUNTROWS(FILTER('Ids','Ids'[PreviousTaskId] = 'Ids'[Id]))

Table:

Result:

Kind regards,
Seanan
If this post helped, please consider accepting it as the solution.

[Anonymous]

Hi @Seanan for some reason I can't get any results from the measure

[Seanan]

Hi @Anonymous 

It appears you've created this as a calculated column. Could you please create this as a measure and then you can use it in a table visualisation.

[Anonymous]

Hi @Seanan It seems that the end result  is the same when using it to measure. Might have something to do with blank spaces 🤔

[Seanan]

Hi @Anonymous 

Would you be able to send the PBIX without any sensitive data so I can take a look?

[Anonymous]

Hi @Seanan here is a link to pbix in google drive 

https://drive.google.com/drive/folders/19qe1EfS4nS0lTvHAf0JyEPlbtGWXrnPw?usp=sharing

[Seanan]

Hi @Anonymous 

I've found the issue as to why the measure is returning 0 rows. In the dataset non of the previousId's are matching the Id column. For example, the measure will check row 10 of the previousId column and see if row 10 in the Id column is a match. This means that it is not looking for row 10 of previousId across the entire Id column. 

Is the expected result to look for a previousid across the entire Id column?

[Anonymous]

Hi @Seanan . These ID:s actually describes employee positions. Id is the position id, previous task id's are telling that is a superior for the  particular position etc. So there is a superior-employee hierargy represented and the employee_ttaskresourceid is the person who holds the position

This is a bit tricky as this hierargy is not represented in any other form. For example nowhere in the db there is no clear indicator who is a superior and who is not and I need to report employee work leaves by the superior etc.

here is a sample pic from dev. environment what this data represents

[Anonymous]

@Seanan Hi, and thanks for your effort to help with this case. Ashis provided workable suggestion and I resolved the case.