Forum Discussion

MichaelSamiotis's avatar
MichaelSamiotis
Resolver I
3 years ago
Solved

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

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

General Comment
Need Help
Show and Tell
  • BrianConnelly's avatar
    BrianConnelly
    3 years ago

    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

4 Replies

    • MichaelSamiotis's avatar
      MichaelSamiotis
      Resolver I
      3 years ago

      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's avatar
        BrianConnelly
        Resolver III
        3 years ago

        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
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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.