Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

DAX - How to find the latest date based the Customer IDs belonging to a single Customer Name

Hi,
I have Dim_Customer table with the following:
PK_Cust_ID              Cust_Name
111                          TKL Supplies
112                          TKL Supplies
113                          TKL Supplies
289                          OCCAs
29900                      OCCAs

The Dim_Customer.PK_Cust_ID column joins to the Fact.FK_Cust_ID table. The Fact table looks like this:
FK_Cust_ID               Date                        Sales         Depot
111                           01/02/2019            122.00       22
111                           20/03/2019            8.00           22
112                           01/01/2019            28.00         22
111                           23/05/2019            61.00         28
289                           20/05/2019            88.00         22
113                           20/02/2019            112.00       89

I should say there is no slicer placed on the Cust_Name in the report. The only context filtering used is the Cust_Name column within a Matrix visiual.

What I need to be able to calcuate is the latest (last) date for the Cust_Name regardless of what Cust_IDs the Cust_Name may have, or the Depots the transaction is recorded against.

 

For example, in the Matrix visual, for the row where the Cust_Name is TKL Supplies I need the measure to return the 23/05/2019 as this is the latest date present for the TKL Supplies customer, regardless of the customer's Cust_ID or Depot.

 

How do I write this measure?
Of course, the measure will need to work out the same for every customer (Cust_Name) so the measure will need to take into account the Matrix visual's context filtering.

 

Thanks.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I guess that there is a one-way relationship between your DIM and your FACT. So when you put your fact in your matrix, the DIM is not filtered against, therefore there is no Filter context applied.
The weird aspect is that you have several Customer IDs for the same Customer Name...

First of all, for simplicity I would add the Cust_name in the fact with a custom column
Cust_Name=RELATED(Dim_Customer[Cust_Name]) 

 

Then another custom column (or a measure if the other filters changes context...)
LastDate=
VAR thisCustName=SELECTEDVALUE(Fact[CustName])

RETURN
MAXX(FILTER(Dim_Customer;Dim_Customer[Cust_Name]=thisCustName);Dim_Customer[Date])

The MAXX iterates along all of the filtered rows and choose the max value.

Should work

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

I guess that there is a one-way relationship between your DIM and your FACT. So when you put your fact in your matrix, the DIM is not filtered against, therefore there is no Filter context applied.
The weird aspect is that you have several Customer IDs for the same Customer Name...

First of all, for simplicity I would add the Cust_name in the fact with a custom column
Cust_Name=RELATED(Dim_Customer[Cust_Name]) 

 

Then another custom column (or a measure if the other filters changes context...)
LastDate=
VAR thisCustName=SELECTEDVALUE(Fact[CustName])

RETURN
MAXX(FILTER(Dim_Customer;Dim_Customer[Cust_Name]=thisCustName);Dim_Customer[Date])

The MAXX iterates along all of the filtered rows and choose the max value.

Should work

Anonymous
Not applicable

@Anonymous 

Thanks for your response. I shall attempt to implement it now.

This is what I have tried in the mean time, without joy. Can you see what I'm thinking in my attempt and could it work with the right tweaks (what are they)?
(the table/field names have changed slightly from my initial post)

 

WEEKS SINCE LAST TRANSACTION AT ALL DEPOTS =

VAR __Customer = SELECTEDVALUE ( Dim_Customer[PK_CustomerID] )
 
VAR __MaxDate =
CALCULATE(
                         LASTDATE( 'Fact'[PostDate] )
                        , ALL( Dim_Depot[PK_DepotID])
                        , CALCULATETABLE( Dim_Customer
                                                               , ALL( Dim_Customer )
                                                               , Dim_Customer[K8 Customer Name] = __Customer
                                                             )
                        )
RETURN __MaxDate
Anonymous
Not applicable

A calculateTable *inside* a Calculate with a Few ALL() around...you like headaches, don't you? 🙂
That means two context transitions plus a couple of ALL that modify filters...

First of all the CALCULATETABLE is needed when you have relationships, but on the one-side. What you want to do (i guess) with this piece:

 
CALCULATETABLE( Dim_Customer
                                                               , ALL( Dim_Customer )
                                                               , Dim_Customer[K8 Customer Name] = __Customer
                                                             )
 
Is to find all customers of your row one. But you are working within the same table so there's no need to invoke calculatetable as there's no context transition.

Also LASTDATE does not return a "max" date, but the LASTDATE in a given month (i.e. LASTDATE(14-03-2019) will return 31-03-2019)

Honestly I don't know what are the fixes, because again a CALCULATETABLE within a CALCULATE with to ALLs...is complicated to visualize

 

Anonymous
Not applicable

@Anonymous 
Thanks again. Your solution worked. I just needed to added an ALL() clause on the Depot and all was perfect.
Still interested to know if my attempted (code snippet) in the previous post was close to working and could it work with some minor tweaks?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors