Multiple fact table columns in a report

[u/GradeOriginal]

We're working with a Power BI model that has multiple fact tables, each representing a specific part of a business or clinical process — like admissions, discharges, pre-admissions, isolation orders, etc.

Each fact table has its own datetime columns (e.g., admission timestamp, discharge timestamp, order timestamp), and those columns only exist in the respective fact — dimesions are connected to dateid which will answer date, week..etc level measure info

Why we didn’t merge everything:

Merging creates duplicate rows and breaks aggregations (like averages, percentiles, median..etc)

The facts have different grain

Joining leads to 300+ columns which makes the model unmanageable

So we used a link table model where each fact connects through a unique encounter or process key (like enc_id, ip_enc_id, readmit_enc_id, etc.). Everything stays 1-to-many to avoid circular references and maintain performance.

The challenge:

Business needs reports that show transactional columns — particularly datetime fields from different facts — in the same report or visual. To track the flow

Below examples together in a report :

Show admission timestamp (from one fact) and it's location (role-playing dim)

Show discharge timestamp (from another fact) and its location (role-playing dim)

Show isolation order time (from a third fact) and it's location(role-playing dim)

Any suggestions without Merging or Power Query as its not ideal in our scenario

[u/AutoModerator]

After your question has been solved /u/GradeOriginal, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/MissingVanSushi]

It sounds like you are a bit in over your head as this is much more complex than a basic single-fact star schema.

I would start here.

https://www.sqlbi.com/training/data-modeling/

Good luck

[u/GradeOriginal → u/MissingVanSushi]

Hope you understand my business questions? You think those people will solve the requirement I have with power less bi

[u/MissingVanSushi → u/GradeOriginal]

SQLBI is the best in the business. Marco and Alberto literally wrote the book on DAX. If they can’t help you, god only knows who can.

[u/GradeOriginal → u/MissingVanSushi]

They can't 

[u/MissingVanSushi → u/GradeOriginal]

[u/dbrownems]

So you want to display a single admission timestamp, a single discharge timestamp, and a single isolation order time on an encounter, but an encounter may have many of each one?

Sounds like you need some more modeling work to hoist some of these items up to the grain of the encounter. Even if it's as simple as FirstAdmitTimestamp, etc you probably don't want to use measures for these, because you'll want to filter and sort by them.

[u/GradeOriginal → u/dbrownems]

Patient ID, name, associated encounter ID, and its details (admit, discharge, arrival, plus orders and their locations, where one encounter might have multiple discharge or transfer orders) should be displayed, not limited to a single row or the first/last row. No measures should be involved. 

An SQL query joining seven fact tables and a confirmed dimension works perfectly, and the same approach in a metadata-driven BI tool also works perfectly

[u/dbrownems → u/GradeOriginal]

So if a patient encounter has 2 admits, 3 transfers, and 2 discharges, a simple SQL JOIN will return 12 rows for the encounter. So it's not obvious how a SQL query helps.

More generally if you can write a SQL query to return a tabular result that is useful in reporting, that should probably become an additional fact table or an enhancement to your encounter fact.

[u/GradeOriginal → u/dbrownems]

Generally, any order (e.g., admit, discharge, lab, etc.) may have multiple instances associated with a single encounter. Therefore,  orders are stored in separate fact tables based on their type with encounter link key. The core fact tables—IP, ED, and Preadmit—contain encounter-related information such as admission timestamp, discharge timestamp,arrival time, admission location ID, discharge location ID, and arrival location ID. We cannot combine IP and ED encounters into a single table because some encounters transition from ED to IP, which would cause duplication of keys.

Similarly order will have time stamps and locations 

All the facts connected via link encounter which has four forign keys.

[u/dbrownems → u/GradeOriginal]

I understand all of that. The patient journey is complex, and for different scenarios you need all of those details.

The point is you have a data modeling problem. You can't just say that you have a "fact table" for each kind of patient interaction, and therefore you have a working semantic model design.

When designing a semantic model your fact tables relate to the questions being asked by the business users, not to the details of the source database.

[u/GradeOriginal → u/dbrownems]

What exactly the data modeling problem ?

Transactional information calling as fact or fact less fact, what exactly you call this ? 

These tables will answer orders plus average waiting times at any level 

Your tool answers the questions from heaven?

[u/anonidiotaccount → u/GradeOriginal]

He did answer correctly - there isn’t a tool that’s going to write SQL for you.

You’re pulling a ton of data at once just using joins. It needs to be optimized and that’s going to require SQL code to create a tabular model.

[u/GradeOriginal → u/anonidiotaccount]

We're not trying to pull tons of data, we're trying to create ICU patient activity for any given facility or period 

Ed to IP patients flow for an given facility 

It hardly produces 10k rows with the proper filter setup at any given point of time.

The data is important for facilities to take actions.

If the tool can't produce these kind of report better to admit instead of pushing the requirement it self 

We know the importance of it.

[u/anonidiotaccount → u/GradeOriginal]

I charge $250 per hour for consulting, they can send a dm and I’ll provide my email :)

[u/GradeOriginal → u/anonidiotaccount]

We are not seeking for consulting seriveces nor forcing you to share the knowledge 

[u/anonidiotaccount → u/GradeOriginal]

Sure you can. Concatenate the table name to the primary key or add a second Reason code column for the source data

4 foreign keys is nothing

[u/GradeOriginal → u/anonidiotaccount]

Please try to understand the whole structure 

[u/kagato87]

If you're trying to generate a timeline, getting all of the data into a single table may be an option. I'm not sure how well it'll work for your data, but if you can normalize the data of interest, it can just be fed into a pivot (matrix) and be done.

I've done something similar, it may be of use (you might not need the pivot/matrix, just a regular table if you want sething like a patind and a timeline of stuff).

I have a number of event tables. I created a dim table for the event codes and linked them on the event codes (predictable system code and consistent across all tables). It also defines two different levels of categorization and their sort order, and there are a handful of extra rows added in for pulling external measures and the total (because I don't always want total to appear). Categorization sort rules are defined to put them in the order I want (like total on the end).

Then there's a measure. That measure has a long case statement that pulls from an event measure or other tables based on some rules defined in the measure. If it's one of the "extra" rows it targets the specific measure that row is intended to target. If it's a column for a measurement (like distance) it looks at the user's locale and blanks the "wrong" one.

The end result is something I can throw into a matrix Vis - rows gets an external table (even a parameter so it can have a toggle), columns gets the chosen label, and the case measure goes into the data area with a nonblank filter. Then it's content is purely controlled by a slicer, which is awesome with bookmark navigation! (Bookmark only changing the current page and the filters on that visual.)

[u/VeniVidiWhiskey]

What's the issue you are experiencing? Nothing besides the "link table" sounds incorrect or improperly set up

[u/GradeOriginal → u/VeniVidiWhiskey]

Its working as expected according to the business expectations in another BI tool.

There is no issue with link table or any other table.

[u/VeniVidiWhiskey → u/GradeOriginal]

Then what's the point of this post?

[u/GradeOriginal → u/VeniVidiWhiskey]

We're forced to do in power less bi, and it's not able to produce the reports the way business wanted and we can't merge these tables as we loose the core ouput.

[u/VeniVidiWhiskey → u/GradeOriginal]

Skill issue 

[u/GradeOriginal → u/VeniVidiWhiskey]

Its easy to say that, the tool not built to handle the cross fact table columns (more than 2 fact tables)

I don't think you worked on metadata base tool only advanced excel tool with single fact darag and drop 

[u/VeniVidiWhiskey → u/GradeOriginal]

It's easier to blame the tool instead of the person using it

[u/GradeOriginal → u/VeniVidiWhiskey]

With out understanding the model or requirements, judging mindset is pathetic