Issue with Star Schema, Joins look wrong when the fact is 0

[DebbieE]

I have a simple Star Schema, all one to many single direction to the fact table

The Date table is used in lots of other models and has dates from 1990 to 2035 in it.

This model only has 10 years of data and its important that the latest date is not the current date but the last date in the data

This model is at year level

I add a table to test flags and add year from date

I add year from the date table

And I add Flags from the flag table

And This works fine. There are only flags to 2003 to 2019 so all the years not attached to these are ommited and its all fine.

Then I add Activities measure from the fact table and it all goes wrong (Ive included the Keys to show)

And its completely wrong. Note that the Key (dateKey from dim to fact, FlagKey dim to fact) are essentially cross joining. There should only be one year.

I ran performance analyser to get the query

// DAX Query
DEFINE
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP(
'Date'[DateKey],
'ActivitiesByYearMetrics'[DateKey],
'Date'[Year],
'YearMonthFlagsJunk'[FlagJunkID],
'ActivitiesByYearMetrics'[FlagJunkID],
'YearMonthFlagsJunk'[Five Years Inc Current Year Latest Report Date Flag],
'YearMonthFlagsJunk'[Ten Year Flag]
), "IsGrandTotalRowTotal"
),
"Total_Activities", 'ActivitiesByYearMetrics'[Activities]
)

VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
'Date'[DateKey],
1,
'ActivitiesByYearMetrics'[DateKey],
1,
'Date'[Year],
1,
'YearMonthFlagsJunk'[FlagJunkID],
1,
'ActivitiesByYearMetrics'[FlagJunkID],
1,
'YearMonthFlagsJunk'[Five Years Inc Current Year Latest Report Date Flag],
1,
'YearMonthFlagsJunk'[Ten Year Flag],
1
)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
[IsGrandTotalRowTotal] DESC,
'Date'[DateKey],
'ActivitiesByYearMetrics'[DateKey],
'Date'[Year],
'YearMonthFlagsJunk'[FlagJunkID],
'ActivitiesByYearMetrics'[FlagJunkID],
'YearMonthFlagsJunk'[Five Years Inc Current Year Latest Report Date Flag],
'YearMonthFlagsJunk'[Ten Year Flag]

I had to add a filter on the measure to get it to work properly

For these reports the user does want to see where years have no metrics data but I cant use my 10 year flag because it doesn’t work.

Can anyone explain to me what is going on here? It feels like a bug in Power BI to me. It feels like it just doesn’t use the join to the central table when you have 0 or null in th fact table.

I cant apply my 10 year flag because its in the flag dimension

I cant set relative date because its not relative to the current date, its relative to the last date in the data

this is now causing some serious issues

Any help or explanation on this would be great

[DebbieE]

I think I have kind of cracked it. 

When you allow your table of visual to show everything, not just values, but you want your flags to filter the results. the Flag cant filter the results because the flag table cant filter the date table without a filter being applied in the central fact table

removing the single direction joins from date and the flag table allows the flag table to now filter date, even when the measure is zero

the reason in the table that the Keys dont match is I believe this filter issue, once your central fact can be 0.

Another way would be to merge the Date and Flag table together so its one table

[PaulDBrown]

@DebbieE 

Interesting!

how about keeping the single but filtering the date table using 
COUNTROWS(
CALCULATETABLE(VALUES(Date table[Date]), RELATEDTABLE(Fact Table) ) ) and use it as a filter (where value =1) in the filter pane on the Dates field?

[DebbieE]

Another good one.

I also throught of merging in power Query editor, taking the 10 year flag and filtering. I would prefer both those options over the multi direction relationship

[PaulDBrown]

What's the measure?

[DebbieE]

Activity. You can see it in the picture as 0, and then I filter it to above 0 in the next image

[PaulDBrown]

@DebbieE 

But 0 is a value. Blank values will not be included.

[DebbieE]

It is a measure and some are blank values and as you can see they have appeared in the table as 0