Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Filtering Table rows by earliest date

Hi all, A relative new DAX user here - so a simple topic I am sure. I have created a table (Services Table) which shows the start date of every service that our customers have. I have created a 2nd...
FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi Anonymous 

try like:

1) create a calculated table like:

Customer = 
ADDCOLUMNS(
    VALUES(Service[CustID]),
    "ServiceID",
    CALCULATE( MIN(Service[ServiceID])),
    "Date",
    CALCULATE( MIN(Service[Date]))
)

2) join service and customer table on the ServiceID column

3) add a calculated column in Service table like:

Type = IF(RELATED(Service[Type])<>"Mobile", "Others", "Mobile")

it worked like:

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Almost legendary thankyou  FreemanZ , I've run it on a real data set and the customer table mostly pulls exactly what I want, the exception is where a mobile and an "other" service have the same start date, and it is the earliest date (for a given customer). DAX has to pick one sevice ID over the other, and I'm not sure of its logic. Can we force it to pick Mobile within the Cust table code when there is a choice to be made?

 

My other challenge is creating the relationship between the cust table and the services table, Im getting a Circular Dependnecy alert - which is very surprising and probably something seperate for me to trouble shoot.