Forum Discussion
Relationships between Fact tables
Hello brokencornets ,
its better to work it as fact and dimensions as the star schema data model
https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
and when the dimensions that are related to all three tables are linked to them, you could easily visualise the values from the three tables alongside each others by the dimensions.
another thing, and this if it's feasable, you can combine the fact tables with each others using merge
check it out https://learn.microsoft.com/en-us/power-query/merge-queries-overview
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Follow me on Linkedin
Vote for my Community Mobile App Idea 💡
Thanks for this. I've tried merging all four fact tables (jobs, WOs, tasks, labour) together and while it works, it seems to be much slower in terms of performance - presumably this is because I'm having to use DISTINCTCOUNT in my measures rather than COUNTROWS? Or is it just because of the size of the new table?
For instance, a measure I had for counting oustanding jobs was as follows:
CALCULATE (
COUNTROWS ( 'Jobs' ),
FILTER (
'Jobs',
( 'Jobs'[Date Reported] <=LASTDATE('Date'[Date])
&& ('Jobs'[Date Completed] > LASTDATE('Date'[Date])
)
)),REMOVEFILTERS()) +
CALCULATE (
COUNTROWS ( 'Jobs' ),
FILTER (
'Jobs',
( 'Jobs'[Date Reported]<=LASTDATE('Date'[Date])
&& (ISBLANK('Jobs'[Date Completed])
)
)),REMOVEFILTERS())
Outstanding =
CALCULATE (
DISTINCTCOUNT('Fact'[Job Number]),
FILTER (
'Fact',
( 'Fact'[Job Reported Date] <=LASTDATE('Date'[Date])
&& ('Fact'[Job Finish Date] > LASTDATE('Date'[Date])
&& not(ISBLANK('Fact'[Job Number]))
)
)),REMOVEFILTERS()) +
CALCULATE (
DISTINCTCOUNT('Fact'[Job Number]),
FILTER (
'Fact',
( 'Fact'[Job Reported Date] <=LASTDATE('Date'[Date])
&& (ISBLANK('Fact'[Job Finish Date])
&& not(ISBLANK('Fact'[Job Number]))
)
)),REMOVEFILTERS())
and it works, but it's 10x slower when sorting tables, etc. How can I get around this?