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

DAX DAX Calculations
1

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…

EVALUATE
SUMMARIZECOLUMNS(‘Date’[Year],
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).

Thanks!

2

@MrRivers
Try this logic

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

3

Got it!

EVALUATE =
DISTINCT(
SELECTCOLUMNS(
NATURALINNERJOIN ( DimDate,  FactSales ),
"DistincYear", [Year],
"MonthName", [MonthName]
)
)

Thanks!

4

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!