cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jim_lucht
New Member

is a temp table-like function possible?

Hi Folks

 

I'm working with a large medical claims file in a Tabular data model in Connect Live mode and am looking for a way to get ALL claims for individuals who have ever had a particular procedure.

 

In SQL I would create a temp table with the person IDs of those who have had Procedure X and then join that back to the claims table to grab all claims under that person ID.

 

I've poked around quite a bit and haven't been able to find a solution. Perhaps there is a complex DAX function we can add to the Tabular Model that can add a flag field based on hard-coded value? Perhaps this is something that's easier to do by hitting the Tabular model with Dax Studio or the Excel plugin?

 

Many thanks for any help and ideas you can lend!

 

Jim

 

2 REPLIES 2
OwenAuger
Super User
Super User

Hi @jim_lucht

 

The structure of what you are wanting to do is basically Basket Analysis:

http://www.daxpatterns.com/basket-analysis/

 

If you want to calculate specific measures filtered to claims for those individuals who have ever had a particular procedure, you could use the pattern on that page with a 'Filter Procedure' table with inactive relationship.

 

Possibly another approach would be to have a PersonID-Procedure bridge table, related to your PersonID column with a bidirectional relationship - haven't tried that before myself.

 

Cheers,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

@OwenAuger

 

Thanks so much for your reply. It hit the nail on the head and has given me some excellent ideas.

 

I'll report back soon.

 

Jim

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!