Pivot fields to filter visual

[fernandoC]

Hi!

I'm trying to create a pie chart that counts the source of a candidate 'only' if it has been hired. To do this there's two steps in the process:

1- That the 'Offer Resolution' column is = "Accepted", LinkName = OfferedBy - Offer for candidate

2- That there's a link between the Offer and the candidate on point #1 as the candidate is the one that contains the "Source" field. 

I would like to have 1 row that contains both the source info and the accepted status. Not sure if it can be achieved by using a column pivot. 

If additional information is required please let me know.

Best,

[vanessafvg · Accepted solution]

hi this is not the most elegant solution, i would probably try and merge them in power query but since i dont have access to the source file what i have done is created a new table called offer accepted, and filtered that only on accepted offers.  I then join that back to link main info on the destination id = source id (look in the relationship view how i have joined them) and then created a measure that does that join with the conditions.

im sure there is a better way to do this but it works.

[fernandoC]

Hi @vanessafvg ,

You gave me a great Idea. At the end I was able to merge the issue link table and connect them with the 'candidates' data on the Source ID and the 'Offers' ID for the Destination ID. That gave me the option to filter by both Offer Resolution and to have the Source information in the same row. 

Theres four hires and the results show 4 total sources which resolves the issue. 

Thanks again for all your help.

Best,

[vanessafvg]

well done!

[vanessafvg]

can you share some sample data?

[fernandoC]

Hi @vanessafvg ,

Thank you for your help on this!.

Sure, I just uploaded a file in Drive on my previous message.

Please let me know if you can access it.

Best, 

[fernandoC]

Hi @vanessafvg @amitchandak,

Was the file accesible for you?.

Please let me know if further information is required.

Best, 

[vanessafvg]

hi yes the file was accesible, not really 100% what you asking for, i dont know if this answers your question, or do you want ot demonstrate what you expect the answer to be?

[fernandoC]

Hi @vanessafvg ,

Thank you for your reply. 

I'm looking to have a pie chart that counts the 'Sources' only when the column 'Offer Resolution' is 'Accepted'. What is happening is that the Sources are only present in Issue Type [Candidates] and the Offer Resolution only on Issue Type [Offers]. I would like to have them both in the same row in order to filter by Offer resolution. 

Any further questions please let me know.

Best,

[vanessafvg]

Hi again

how do you know which candidate has accepted the the offer, what links the candidate row to the offer resolution row? in order ot join them up you need to know the relationship between them.  i have had  look at the ids but there are some duplications and you have quite a lot of columns.    Also which  is the table you using link main info?   

so what i need from you is

a) how do you know the candidate that an offer has been given to, what identifying information helps you to join these 2 pieces of data together is it an id? if so which id.

b) which table are you referrig to from what i can see you are using the link main info.  

please give more information about which tables need to be used and what is the join between issue types of candidate to offer.

[fernandoC]

Hi @vanessafvg ,

Please find my comments on your questions:

a) how do you know the candidate that an offer has been given to, what identifying information helps you to join these 2 pieces of data together is it an id? if so which id. Yes!, the table that contains the link information is 'Link Main Info' under the column 'LINKNAME' with the category OfferedBy-Offer for candidate and there's two ID's the Source ID and Destination ID in this case I think the ID of the offer should be under the Destination ID column. 

b) which table are you referrig to from what i can see you are using the link main info.  Yes the table used is Link Main Info. 

Any further questions please let me know.

Best,

[vanessafvg]

hi unfortunately i am about to go away so i dont have time to assist maybe @amitchandak  wants to step in and help. 

otherwise i can help next week.

[fernandoC]

Hi @vanessafvg ,

Thank you for your help.

@amitchandak is there any additional information that I can share to you?.

Thanks in advance.

Best,

[amitchandak]

@fernandoC ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[fernandoC]

Hi @amitchandak ,

Thank you for your help!.

Please find the pbix file here: File

The main information is located on the table "Link Main Info" table. 

Any other questions or doubts please let me know.

Best,