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]
)
