Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
fernandoC
Helper V
Helper V

Pivot fields to filter visual

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. 

 

requisition.PNG

 

If additional information is required please let me know.

 

Best,

1 ACCEPTED SOLUTION
vanessafvg
Super User
Super User

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.

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

14 REPLIES 14
vanessafvg
Super User
Super User

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.

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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. 

 

Solution.PNGSolution 2.PNG

 

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

 

Thanks again for all your help.

 

Best,

well done!




If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




vanessafvg
Super User
Super User

can you share some sample data?




If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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, 

Hi @vanessafvg @amitchandak,

 

Was the file accesible for you?.

 

Please let me know if further information is required.

 

Best, 

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?

 

 

Capture.PNG





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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. 

Sources correct.PNGSources incorrect.PNG

 

Any further questions please let me know.

 

Best,

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.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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,

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.

 
 
 
 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Hi @vanessafvg ,

 

Thank you for your help.

 

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

 

Thanks in advance.

 

Best,

amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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, 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.