Returning the distinct values from a Dim column based on the values from a Fact Key

Dear colleagues, I would like to populate a parameter list based only on the content already loaded in my fact table.

I was trying to return just the years that have a sale instead of all the years loaded into my DimDate table

I tried to use SUMMARIZECOLMUNS with FILTER clause but it didn’t work…

FILTER ( ‘Date’[Datekey] ) IN VALUES ( ‘FactSales’[DateKey] ))

Has someone faced a similar problem? It seems so basic but even thought I could find a way to do it in DAX (Shame on me).


Try this logic

Table = SELECTCOLUMNS( FILTER( Date, ‘Date’[Datekey] IN VALUES (‘FactSales’[DateKey] )), “Year”, ‘Date’[Year] )

Got it!

NATURALINNERJOIN ( DimDate,  FactSales ),
"DistincYear", [Year],
"MonthName", [MonthName]


Hi @MrRivers, your response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!