Report Filters showing ALL values in Dimension Tables

[DepthOfFocus]

Hi All - Relatively new to PBI development here!

I have a very simple data model, consisting of Transaction_Fact table, and a Calendar Dimension table.

It joins effortlessly using the report_date in the FACT table to the CALENDAR_DATE in the Calendar dimension table.

The transaction Fact table only contains transactions for Aug 2020 until Dec 2020. 

Anytime I use Year/Month filters from my calnder dimension, the filters shows ALL values in the dimension, even though, they don't exist within the fact table: 

How can I fix this so that I only has RELEVANT values that pertain to those in the source? It's almost as if Power BI is doing a RIGHT JOIN to the dimension.  This happens with all my other Dimensions as well, including my employee dimension. Employee names that don't have a single record/transaction in the fact table, are still showing up in the report filters.

ANY help is appreciated! 

[v-yangliu-msft · Accepted solution]

Hi  @DepthOfFocus  ,

You put the calendar table into the slicer, and the dates in it will contain the data in the fact table, and even other data. You can use calendar to create a calendar table, which will make the calendar more consistent

 Create a table:

Use function:

Table 2 = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))
The new calendar is as follows:

This is about calendar:：

https://docs.microsoft.com/en-us/dax/calendar-function-dax

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Generate the Calendar Table with the following Table formula

Calendar = calendar(min(Transaction_fact[report_date]),max(Transaction_fact[report_date]))

Hope this helps.