How to show filter values that data is not empty

[KylieF8439]

I have two tables.

Table 1: DimDate

Table 2: FactStudentCount

I build a report using these two tables:

The "Date" is the filter. I hope only those Date_Key values that are present in FactStudentCount table are shown in the report filter. In this example, I hope the "Date" filter in the report shows "Wednesday, 31 January 2024" and "Thursday, 29 February 2024" only.

How to achieve this? Thank you in advance.

[Anonymous · Accepted solution]

Hi lbendlin ,thanks for the quick reply, I'll add more.

Hi @KylieF8439 ,

Use the following dax expression to create a table and establish table-to-table relationships.

Table = CALCULATETABLE(VALUES(DimDate),'DimDate'[Date_Key] IN VALUES(FactStudentCount[Date_Key]))

Best Regards,
Wenbin Zhou

[lbendlin]

Why would you want to do this?  This goes against design guidelines.

How to do it:  Change your data model to make the connection go in "both" directions so that the fact table can filter the dimension table.