Help requested with analysing my practice data

DAX DAX Calculations
2

@jps,

Your DAX is fine. I have simplified your Age Group measure through the use of a variable, but the logic remains the same:

Age Group = 

VAR Age = YEAR(TODAY() ) - Customer[Year_Birth]
VAR Result =
SWITCH(
    TRUE(),
   Age <= 20, "below 20 yrs", 
   Age <= 30, "20 - 30 yrs",
   Age <= 40, "30 - 40 yrs",
   Age <=50, "40 - 50 yrs",
   Age <= 65, "50 - 65 yrs",
   Age <= 85, "65 - 85 Yrs",
   "Above 85 yrs"
)
RETURN
Result

The problem is in your data modeling. IMO, there should be only three tables in this data model: a Dates table (very strongly recommend using Enterprise DNA Expert @Melissa’s Extended Date Table and also downloading the accompanying Cheat Sheet), a Customer dimension table containing all the attributes of a customer that don’t deal with purchasing behavior (that info goes in the fact table I’ve called Data) and are unlikely to change much over time. The three table are organized in a standard star schema, with one to many relationships, and the Dates table is marked as a date table. Here’s what that model looks like:

image
image1045×567 18.3 KB

Your model subdivided the customer attributes into each of their own separate tables, which forced you to create a lot of artificial, unnecessary key fields, that in turn broke the DAX measure.

WIth the new data model, the simple [Wine Total] measure calculates accurately in context:

image
image736×375 17.1 KB

I would strongly recommend that you take @sam.mckay’s excellent portal course entitled “Advanced Transformations and Data Modeling”, where he goes over all of these concepts in detail.

I hope this is helpful. My full solution file is attached.