Slicer using DimDate do not ge filtered based on Fact

[Anonymous]

I have created DimDate with calculated colums. I have report with Stacked Column chart, Year slicer and WeekNum slicer.

I have now difficulties to use DimDate as slicer. I have data from only 2019 in fact table which have been connected to DimDate. DimDate covers 2019-2013 dates.

However my slicers for Year and WeekNum shows every single rows that exists in DimDate. How to configure slicer and relationship so that Slicer get only values that includes data in Fact.

[Anonymous]

Hi , 

Create your date dimesion with below DAX.

DateTable =

ADDCOLUMNS (

CALENDARAUTO(),

"Year", YEAR ( [Date] ),

"QuarterOfYear", FORMAT ( [Date], "Q" ),

"MonthOfYear", FORMAT ( [Date], "MM" ),

"DateInt", FORMAT ( [Date], "YYYYMMDD" ),

"MonthName", FORMAT ( [Date], "mmmm" ),

"MonthInCalendar", FORMAT ( [Date], "mmm YYYY" ),

"QuarterInCalendar", "Q" & FORMAT ( [Date], "Q" ) & " " & FORMAT ( [Date], "YYYY" ),

"DayInWeek", WEEKDAY ( [Date] ),

"DayOfWeekName", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ))

Best Regards,
Mail2inba4

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

[Anonymous]

Seems to work. Thanks.  How to add Week Number to the script? (1-52)

[Tahreem24]

=weeknum(Table[Datecolumn])

Don't forget to hit Thumbs up and mark it as a solution if it helps you.

[Anonymous]

WeekNum Slicer did not work correctly. It showed all possible values without filter to Fact.

I did added Calculated Column with DAX: WeekNum =weeknum(Table[Datecolumn]) to same table, which have been created with Advanced Editor.

I wonder if it is possible to add week number inside script in Advanced Editor.

[Tahreem24]

Change the relationship cardinality to one to many and cross filter direction to both.

Please hit Thumbs up for support and accept this as a solution if it helps you.

[Anonymous]

I tried "many to one" with "both" and "one to many" with "both". No positive effect to slicers.