Data modelling with Dim dates[Year] doesn't work

[MichaelSamiotis]

Hello all.

I have the below DAX (1) which doesn't work (returns nothing) and I don't get why. When I change it to the second DAX code (2) it works fine. I have a 1-* relationship between Dim Dates and Fact Sales. Can someone please help? I was expecting the year filter to filter the fact sales and propagate the dim dates[date] filter to the fact sales but this doesn't happen. The Year column is a whole number in power query. Thank you all.

(1)

Customers =

VAR calculation =
CALCULATE (
DISTINCTCOUNT ( 'Fact Sales'[customer_id] ),
'Dim Dates'[Year] = YEAR ( TODAY() )
)
RETURN
calculation

(2)

Customers =

VAR calculation =
CALCULATE (
DISTINCTCOUNT ( 'Fact Sales'[customer_id] ),
YEAR (
'Fact Sales'[sales_date]
) = YEAR ( TODAY() )
)
RETURN
calculation

[Ashish_Mathur]

Hi,

Assuming the relationship (Many to One and Single) is from the Sales_date column of the Fact sales table to the Date column of the Dim Dates table, create a Year slicer from the Dim Date table and select 2023 there.  This measure should work

Measure = DISTINCTCOUNT('Fact Sales'[customer_id])

Hope this helps.

[KeyurPatel14]

Hi @MichaelSamiotis ,
Can you please provide your pbix file? 
Because I think it should work as you expected.

[MichaelSamiotis]

Sorry can't due to data protection issues but I really don't get why it doesn't work. It should. Data formats are both integers (whole numbers), I have a 1 to * relationship on the date filed.

[BrianConnelly · Accepted solution]

Is the relationship Active?  Your code should work as written so something else is going on.  Could try this code to test out the variables....

Customers =
VAR theYear = YEAR ( TODAY() )
VAR currentDateYear = MAX(Dim Dates'[Year])
VAR calculation =
CALCULATE (
DISTINCTCOUNT ( 'Fact Sales'[customer_id] ),
'Dim Dates'[Year] = theYear 
)
// RETURN calculation  // Uncomment to see the executed value
// RETURN theYear // Uncomment to see the executed value
// RETURN currentDateYear // Uncomment to see the executed value