topic Re: Filtering Table rows by earliest date in DAX Commands and Tips

Filtering Table rows by earliest date

Anonymous — Wed, 10 May 2023 02:05:48 GMT

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 related table (Cust Table) using Summarise to show whether the first service each customer has is a Mobile or not. Here are the two fomulaes I have used so far in the Cust Table:

1: Cust Table = SUMMARIZE('Services Table','Services Table'[CustID])

This is working to create the customer ID collum.

2: Service ID = LOOKUPVALUE('Services Table'[serviceid],'Services Table'[CustID],'Cust Table'[CustID],1)

This almost works, but of course every time there are multiple start dates for a customer it is populating with my test value of "1".

I have been trying to use a range of formula approaches such as first date, filters, calculate to select the right service based on date, but I'm not sharing them because none of them are working, and I may be on totally the wrong path. Please see below the tables I am trying to see (using dummy data).

Any clues for me please team of legends?

Rangi28.

Re: Filtering Table rows by earliest date

FreemanZ — Wed, 10 May 2023 02:51:15 GMT

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:

Re: Filtering Table rows by earliest date

Anonymous — Thu, 11 May 2023 00:34:12 GMT

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.

Re: Filtering Table rows by earliest date

Anonymous — Thu, 11 May 2023 00:43:46 GMT

(Circular Dependancy is sorted out - ignore that please)