DAX formula to show latest client change row

[harringtonc]

Hi all,

I have a DAX formula in Analysis Services:

=MAXX( FILTER('Client Changes',

                          'Client Changes'[dclc_dcli_id]=EARLIER('Client Changes'[dclc_dcli_id])

                         ),

               'Client Changes'[dclc_id]

             )

I use this formula to get the latest change record ID by grouping by each distinct client. This works fine.

(this ID can then be used to compare the latest value of any client attribute with previous values to report on changes)

I also have a relationship between my Dates table and 'Client Changes'[Valid To Date], which is the date on which each change was made.

In PowerBI I have a date selection filter which filters the client change records based on their Valid To Date. This filter works fine.

The issue I have is that I am having trouble getting the Latest Change ID formula above to "see" the date filter, i.e. I only want to show tyhe latest change ID using the client change records filter by the date selection filter not all records.

So what I want to do is to add a date criteria to the filter which will take into account the maximum Valid To Date as specified by the Power BI date filter.

Thanks,

Carl

[Anonymous]

Why are you using EARLIER in your measure even if there is no OUTER row context to reach? What is this filter actually doing:

    FILTER (
        'Client Changes',
        'Client Changes'[dclc_dcli_id] = EARLIER ( 'Client Changes'[dclc_dcli_id] )
    )

I think it's doing nothing since this 'Client Changes'[dclc_dcli_id] = EARLIER ( 'Client Changes'[dclc_dcli_id] ) is ALWAYS TRUE.

Would this measure not do the same thing as yours?

MAXX (
    'Client Changes',
    'Client Changes'[dclc_id]
)

// or even this one:
MAX ( 'Client Changes'[dclc_id] )

By the way, this max should honor any filters that you put on 'Client Changes'. So, if your Date table is tied to a date column in 'Client Changes', then the above should return the max dclc_id that's visible in the current context.

Best

Darek

[harringtonc]

Hi Darek,

Thanks so much for the quick reply.

I'm using EARLIER in the filter to act as a PARTITION BY clause. There are loads of different client records, so without the EARLIER clause then it will return the MAX dclc_id for the entire table, not the MAX dclc_id for each client:

dclc_id	dclc_dcli_id	Valid From	Valid To	CTY	Latest Client Record Id	Current CTY	Calculated Valid To Date	MAX Client Record Id
2037475	161466	01/01/1990	22/01/2015	ITA	2037477	ITA	22/01/2015	2118948
2037476	161466	23/01/2015	28/07/2016	GBR	2037477	ITA	28/07/2016	2118948
2037477	161466	29/07/2016		ITA	2037477	ITA	09/07/2019	2118948

The above data only shows the record for Client 161466 and the change record 2037477 is the latest change record for this client. What I want to achive in my report is when the user selects a To Date of say 28/07/2016 then the Latest Client Record ID would change to 2037476.

The Latest Client Record ID column is calculated using:

=MAXX(FILTER('Client Changes'
            ,'Client Changes'[dclc_dcli_id]=EARLIER('Client Changes'[dclc_dcli_id]
            )
     ,'Client Changes'[dclc_id]
     )

Without the EARLIER clause I get the maximum DCLC_ID in the entire table, Client Record Id:

=MAXX(FILTER('Client Changes'
            ,'Client Changes'[dclc_dcli_id]='Client Changes'[dclc_dcli_id]
            )
     ,'Client Changes'[dclc_id]
     )

I get the same result for your suggestion:

=MAXX('Client Changes'
     ,'Client Changes'[dclc_id]
     )

I'm pretty new to DAX and Power BI so I could be using completely the wrong technique.

But what I'm trying to achive is to change my Latest Client Id formula to expand the filter to compare something (parameter) to the Calculated Valid To Date field in order to get the latest client change record on or before the date slected.

Thanks,

Carl

[Anonymous]

Carl, are you talking about DAX measures or calculated columns? I have a feeling the latter is the case...

Best

Darek

[harringtonc]

Hi Darek,

Yes, these are calculated columns, sorry should have been more specific (complete n00b, only started using PowerBI and Analysis Services last week!).

Is it because they're not setup as Measures that the columns are not date aware?

Thanks,

Carl

[Anonymous]

OK.

First of all, for what you want you cannot have a calculated column, only a measure. Here's the first variation of the measure (since I don't know what version you need as it's not clear from your description):

[Latest Client Record ID] =
var __onlyOneClientVisible = HASONEVALUE( 'Client Changes'[dclc_dcli_id] )
var __dateSelected = SELECTEDVALUE( ValidToDateSlicer[Valid To] )
var __latestClientRecordID =
	CALCULATE(
		MAX( 'Client Changes'[dclc_id] ),
		'Client Changes'[Calculated Valid To Date] <= __dateSelected,
		ALLEXCEPT( 'Client Changes', Client Changes'[dclc_dcli_id] )
	)
return
	if( __onlyOneClientVisible, __latestClientRecordID, "Many Clients Visible")

Here's another variation:

[Latest Client Record ID] =
var __onlyOneClientVisible = HASONEVALUE( 'Client Changes'[dclc_dcli_id] )
var __dateSelected = SELECTEDVALUE( ValidToDateSlicer[Valid To] )
var __latestClientRecordID =
	CALCULATE(
		MAX( 'Client Changes'[dclc_id] ),
		'Client Changes'[Calculated Valid To Date] <= __dateSelected
	)
return
	if( __onlyOneClientVisible, __latestClientRecordID, "Many Clients Visible")

The difference between them is that the first does not honour any filters on the 'Client Changes' table (apart from the one on dclc_dcli_id). The other one does honour all filters on the table apart from [Calculated Valid To Date].

There's still another version that does honour all filters, even on [Calculated Valid To Date]:

[Latest Client Record ID] =
var __onlyOneClientVisible = HASONEVALUE( 'Client Changes'[dclc_dcli_id] )
var __dateSelected = SELECTEDVALUE( ValidToDateSlicer[Valid To] )
var __latestClientRecordID =
	CALCULATE(
		MAX( 'Client Changes'[dclc_id] ),
		KEEPFILTERS( 'Client Changes'[Calculated Valid To Date] <= __dateSelected )
	)
return
	if( __onlyOneClientVisible, __latestClientRecordID, "Many Clients Visible")

Please note that if more than one client is visible in the current context, "Many Clients Visible" will be returned. This is because it makes no sense to calculate latest record id for many clients at the same time.

For the above to work you need a table ValidToDateSlicer that will store all the dates you'd like to use as the Valid To date that your users would select. Please note that this table should NOT be linked to 'Client Changes' in any way. It's a parameter table.

Best

Darek

[harringtonc]

Hi,

Thanks for taking the time to look into this problem, I appreciate it.

Thanks,

Carl

[Anonymous]

No, that's not why I was asking. Bear with me, please. I'll show you the right formula.

Best

Darek