Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
Scenerio:
Each physician in the U.S. is required to be credentialed (a.k.a. - Approved) by an insurance carrier prior to submitting charges for service. Our Credentialling Department wants a report with two visuals:
The obsticle:
The source data is from a SharePoint List that contains 20 columns representing the current insurance carriers. This field was created as a single select radio button
The result is a table structure of choices (full sample data is supplied below)
For the Bar Chart, I need to know how to extract a count without using the filter because they override each other.
My company security policy will not allow me to attach data however, below I copied an pasted in a scrubbed version that I hope is usable.
First Name of Provider | NCTRACKS | AETNA | ALLIANCE | Ambetter | BCBS | CIGNA | EastPointe | EverNorth | HUMANA | MAGELLEN | MEDCOST | MEDICARE | OPTUM Commercial Behavioral Health | PARTNERS | Railroad Medicare | TRICARE(PREV MHN) | Trillium | UHC Commercial Medical | Vaya. | Wellcare |
Janet | Approved | Approved | N/A | Approved | ||||||||||||||||
Jacob | Approved | Approved | Approved | Approved | Approved | Approved | ||||||||||||||
Mary | Approved | Approved | Approved | Approved | Approved | Approved | Approved | Approved | ||||||||||||
Andrew | Approved | Approved | N/A | Approved | ||||||||||||||||
Kacie | Approved | N/A | N/A | N/A | ||||||||||||||||
Kristen | Approved | N/A | Approved | Approved | Approved | Approved | Approved | |||||||||||||
Sydneigh | Approved | Approved | N/A | N/A | N/A | Submitted | Approved | Approved | ||||||||||||
Charles | Approved | Approved | Approved | Approved | Approved | N/A | ||||||||||||||
Joseph | Approved | Approved | Approved | Submitted | Approved | |||||||||||||||
Jazz | Approved | N/A | N/A | Submitted | Approved | |||||||||||||||
Rachel | Approved | Submitted | N/A | Approved | ||||||||||||||||
Jeffrey | Approved | Approved | N/A | Approved | ||||||||||||||||
Gayle | Approved | Approved | Approved | Approved | Approved | Approved | Approved | |||||||||||||
Sarah | Approved | Approved | Approved | Approved | N/A | Approved | Approved | |||||||||||||
Hannah | Submitted | Needed or Possibly Needed | N/A | Submitted | Needed or Possibly Needed | N/A | N/A | Needed or Possibly Needed | ||||||||||||
Heather | Approved | Submitted | Approved | Approved | N/A | Approved | Approved | N/A | Submitted | |||||||||||
Jason | Approved | Approved | Denied | Approved | Approved | Approved | Approved | Denied | Approved | |||||||||||
Ana | Approved | Approved | Approved | Approved | Denied | Approved | Approved | Approved | Approved | Approved | Approved | Approved | Approved | Approved | Approved | Approved | ||||
Angela | Approved | Submitted | Approved | Submitted | Approved | Approved | Approved | Approved | Approved | Approved | Approved | |||||||||
Karimah | Approved | Approved | Submitted | Approved | ||||||||||||||||
Susan | Approved | Submitted | Approved | Approved | N/A | Submitted | Approved | Approved | Approved | N/A | ||||||||||
Lauren | Approved | Approved | Approved | Submitted | Approved | |||||||||||||||
Sarah | Approved | Approved | Submitted | Submitted | Approved | Approved | Approved | N/A | ||||||||||||
Aimee | Approved | Approved | Approved | Approved | Approved | |||||||||||||||
Richard | Approved | Approved | Approved | Approved | Submitted | Approved | Approved | Approved | Approved | Approved | Approved | Approved | Approved | Approved | Approved | |||||
Rebeca | Approved | Approved | N/A | Approved | ||||||||||||||||
Tashema | Approved | N/A | N/A | N/A | Submitted | |||||||||||||||
Beth | Approved | Approved | Approved | Submitted | Approved | Submitted | Approved | Approved | Approved | Approved | N/A | Approved | ||||||||
Brittany | Approved | N/A | N/A | N/A | Submitted | |||||||||||||||
Brandon | Submitted | Needed or Possibly Needed | N/A | Needed or Possibly Needed | Needed or Possibly Needed | N/A | N/A | Needed or Possibly Needed | Needed or Possibly Needed | |||||||||||
Josette | Approved | Submitted | Approved | |||||||||||||||||
Kristina | Approved | Approved | Approved | Approved | Approved | Approved | Approved | Approved | ||||||||||||
Kelly | Approved | Approved | Approved | Approved | Approved | Approved | Approved | Approved | Approved | Approved | ||||||||||
Kaitlyn | Approved | N/A | N/A | N/A | ||||||||||||||||
Amanda | Approved | Submitted | Approved | Approved | Approved | Submitted | Approved | Approved | ||||||||||||
Meredith | Approved | Approved | Approved | Approved | N/A | Approved | Approved | Approved | ||||||||||||
Rachel | Approved | Approved | Denied | Approved | Approved | Approved | Approved | Approved | ||||||||||||
Amanda | Approved | Approved | Approved | Submitted | Approved | Approved | Approved | |||||||||||||
Alyson | Approved | Approved | Approved | N/A | Approved | |||||||||||||||
Christopher | Approved | Approved | Approved | Approved | Approved | Submitted | Approved | Approved | Approved | Submitted | Approved | N/A | ||||||||
Ayrien | Approved | Approved | Approved | Approved | Approved | |||||||||||||||
Ross | Approved | N/A | Approved | |||||||||||||||||
Sean | Approved | N/A | Approved | N/A | Submitted | Approved | ||||||||||||||
Glenda | Approved | Approved | N/A | Approved | Approved | |||||||||||||||
Ashley | Approved | Approved | Approved | Approved | ||||||||||||||||
Breana | Approved | Approved | Approved | N/A | Approved | Approved | ||||||||||||||
Shante | Approved | Approved | N/A | Approved | Approved | |||||||||||||||
Elizabeth | Approved | N/A | Approved | |||||||||||||||||
Lani | Approved | Approved | Submitted | N/A | Approved | Approved | Approved | N/A | Approved | Approved | Approved |
Thanks for any ideas you can share
Ashley
Solved! Go to Solution.
Ashish, This is the EXACT result that I was hoping for! Thanks you so very much! Now....Since my data is being modified weekly, can you tell me how you created this table, and how it will link to my existing data that I pull from SharePoint each week? If I am correct in my assumption, either I will need to add these 3 columns to my existing table or I will need to build a second table (both of which will require a bit more direction from you if possible). Thanks
For other beginners I will add a bit of detail on the process as the Applied Steps were pretty simplistic given the modificatins I'd made to my sample data. So if starting from scratch:
Thanks again Ashish!
Hi,
You may download my PBI file from here.
Hope this helps.
Ashish, This is the EXACT result that I was hoping for! Thanks you so very much! Now....Since my data is being modified weekly, can you tell me how you created this table, and how it will link to my existing data that I pull from SharePoint each week? If I am correct in my assumption, either I will need to add these 3 columns to my existing table or I will need to build a second table (both of which will require a bit more direction from you if possible). Thanks
You are welcome. Please go through the steps in the Applied Steps pane of the Query Editor to understand the transformation i have carried out.
For chart one my hope is to create a bar chart of all the insurance carriers and the number of our clinicians who are "approved" by them. It should look like this:
For item 2, I need a way to display each of the 203 employees with a list of each carrier....sort of like above but this time a visual display of who has what...I have no idea how to best display that or how to get the info in 1 chart as its is in 20 different columns.
Hope that helps.
Oh, that's tricky.
With this table, I think you will not be able to do this calculation, you'll have to create a new one, maybe using PowerQuery M or SQL.
The "sub-query" of this table will have to look something like this to create this graph:
Category | Count_Approved |
AETNA | 123 |
BCBS | 98 |
Trillium | 200 |
Hope it helped.
Regards,
Douglas.
Thanks for taking a look.
Hi @anwilkins ,
I couldn't understand your question, could you please provide a drawing or something like that of what you're expecting to see? Like a Actual vs. Expected visual.
It would help me and the others to help you 😊
Regards,
Douglas.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
59 | |
58 |