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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
WBscooby
Helper III
Helper III

Combining data from unrelated FACT tables or rework schema

Hi

 

I'm hoping for some modelling/ measure advice.

 

I have a schema that has 2 separate fact tables - FACToutcomes and FACTincidents.

FACToutcomes relates to tables outcome, customer and date
FACTincidents relates to tables incident, customer and date
Both of the tables also has a subtype column

Each of the tables relate to the same Customer and date dimension tables but not to each other.

 

To date, I have kept the tables as separate fact tables because outcomes has several other important dimension tables which have no relation to incidents and by combining, I found I was getting lots of null values in my FACT table.

 

However, for business reasons, there are occasions when outcomes and incidents need to be counted together to demonstrate total 'throughput'. This, for instance, would require displaying both the subtypes for outcomes and incidents on the same axis on a chart.

I am going round in circles trying to decide if it is better to keep my model as it is and try to create measures to demonstrate this or if I should rework my model.

 

I have created a measure to calculate the information I require, but can't work out how I then would create an axis to display this on. It's important that the result is dynamic so different date quarters can be selected using a slicer.

Any advice would be greatly appreciated!

 

My measure:

SUMX(
UNION(
SELECTCOLUMNS(
SUMMARIZE(
'FACTOutcomes',
'Outcome'[Type],
"OutcomeCount", COUNT('Outcome'[Outcome_ID])
),
"Outcome", [Type],
"OutcomeCount", [OutcomeCount]
),
SELECTCOLUMNS(
SUMMARIZE(
'FACTIncidents',
'Incidents'[Type],
"OutcomeCount", COUNT('Incidents'[Incident_ID])
),
"Outcome", 'Incidents'[Type],
"OutcomeCount", [OutcomeCount]
)
),
[OutcomeCount]

)

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

2 REPLIES 2
WBscooby
Helper III
Helper III

Thank you,for a really helpful and clear explanation

amitchandak
Super User
Super User

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.