topic Re: MAX Value on multiple dimensions and dynamic date in DAX Commands and Tips

MAX Value on multiple dimensions and dynamic date

vincenzio — Sun, 12 Jul 2020 21:24:40 GMT

Hi DAX Gurus,
I'm doing my first steps in PowerBI, which goes very easy except for one small problem that I can not figure out:
Working with data set that looks as follows:

REPORT DATE	MEMBER	PRODUCT	RN	VALUE
01/02/2018	A	A1	1	30
01/07/2018	A	A1	2	40
28/08/2018	A	A1	3	20
01/03/2018	A	CC	1	44
01/07/2018	A	CC	2	35
26/08/2018	A	CC	3	10
01/02/2017	B	A1	1	4
01/07/2018	B	A1	2	8
28/08/2018	B	A1	3	33
01/03/2018	B	DD	1	66
01/07/2018	B	DD	2	12
26/08/2018	B	DD	3	44

All fields are coming from a database. What I'm trying to accomplish, is filter the results of the table in a way it only sows the record with the max row number for each combination of MEMBER + PRODUCT + REPORT DATE (closest but equal or smaller than the selected date).
For example, if I filter on date March the 4th (04/03/2018) I should see

REPORT DATE	MEMBER	PRODUCT	RN	VALUE
01/02/2018	A	A1	1	30
01/03/2018	A	CC	1	44
01/02/2017	B	A1	1	4
01/03/2018	B	DD	1	66

I have an additional date table available in my model.
Any suggestions are much apricated
Regards,
Vincent

Re: MAX Value on multiple dimensions and dynamic date

lbendlin — Sun, 12 Jul 2020 21:27:02 GMT

DAX has no concept of row numbers. You need to be more specific on what your sort criteriae are.

Re: MAX Value on multiple dimensions and dynamic date

vincenzio — Sun, 12 Jul 2020 21:40:01 GMT

Apologies, I just see that the field I'm revering to as "row number" is called RN in my example

Hi Ibendlin,

The ~~row number~~ RN is a field that exist in the source data. It's counting the transactions for each combination of MEMBER + PRODUCT based on REPORT DATE.
All fields in the example table exists in the source data

Regards,

Vincent

Re: MAX Value on multiple dimensions and dynamic date

lbendlin — Sun, 12 Jul 2020 21:54:48 GMT

This is not a small problem at all. What you are looking for as a return value is a table, not a scalar. How do you expect to display the results?

Re: MAX Value on multiple dimensions and dynamic date

vincenzio — Mon, 13 Jul 2020 07:01:46 GMT

Hi Ibendlin,
I would like to display the results as :

REPORT DATE	MEMBER	PRODUCT	RN	VALUE
01/02/2018	A	A1	1	30
01/03/2018	A	CC	1	44
01/02/2017	B	A1	1	4
01/03/2018	B	DD	1	66

Or in a graph with the same values. Essentially, it’s the last record with the last date for each "MEMBER + PRODUCT" , where the date <= selected date in a filter.

Not sure if leaving out the row number makes it more easy. When it can be solved by only using the date, it’s also fine.
Regards,
Vincent

Re: MAX Value on multiple dimensions and dynamic date

Anonymous — Mon, 13 Jul 2020 13:00:33 GMT

[EDIT 2020-07-13 13:55 UK time] // Assumption: // You have a calendar table with all the dates // you need. This table MUST be disconnected. // Then you create a measure that will, for each row // in the table you've presented, return 1 if the // row qualifies according to your requirement, and 0/Blank // if not. Once you have this measure, you can drop // the fields from the table into a table/matrix visual // and filter the rows in the Filter pane via the measure. // When it's value is 1, show the row. Hide otherwise. // Also, we are assuming that each combination of (Member, Product, RN) // cannot have more than one Report Date assigned. // Here's the measure: Filtering Measure = var __shouldCalc = HASONEFILTER( T[Report Date] ) && HASONEFILTER( T[Member] ) && HASONEFILTER( T[Product] ) && HASONEFILTER( T[RN] ) var __result = IF( __shouldCalc, var __selectedDate = SELECTEDVALUE( 'Calendar'[Date] ) var __currentRN = SELECTEDVALUE( T[RN] ) var __currentMember = SELECTEDVALUE( T[MEMBER] ) var __currentProduct = SELECTEDVALUE( T[PRODUCT] ) // For each combination of (Member, Product, Report Date) // we have to find out if the combination is what we're // looking for. var __shouldRetain = CALCULATE( MAXX( // This one picks up the row from the // table with the latest date, respecting // all the filters under CALCULATE. MAXX // picks up the one and only value of RN // that qualifies for our condition. TOPN( 1, T, T[Report Date], DESC ), T[RN] // The "=" condition returns true if the current // RN is equal to the one that we pick up among // the RN's for the same Member and Product. If // it's equal, then we know we're on the row // that should be returned. ) = __currentRN, // This makes sure that we're only looking at // rows in the table that have Report Date // before or on the selected date. KEEPFILTERS( T[Report Date] <= __selectedDate ), // This one makes sure that for each row in the visual // we're only looking at the rows that have the same // Member and Product as the current row. ALLEXCEPT( T, T[Member], T[Product] ), // Filter only the dates that are visible for the // current Member and Product. CALCULATETABLE( VALUES( T[REPORT DATE] ), FILTER( ALLSELECTED( T ), T[MEMBER] = __currentMember && T[PRODUCT] = __currentProduct ) ) ) return __shouldRetain ) return 1 * __result

Best

Re: MAX Value on multiple dimensions and dynamic date

vincenzio — Wed, 15 Jul 2020 18:24:31 GMT

Hi daxer,
That worked! The only thing I ended up doing was taking out the :

IF( __shouldCalc,

part. (And the closing bracket.) Thanks for this. It's a great starting point for my other formuals too.

Regards,

Vincent

Re: MAX Value on multiple dimensions and dynamic date

Anonymous — Thu, 16 Jul 2020 08:49:16 GMT

Happy that it worked. However, since you removed the guarding condition and I wrote the code under the assumption that the condition is TRUE, please be very careful and test your measures based on the stripped down formula thoroughly. Experience has taught me that if you use ALLEXCEPT in a CALCULATE formula as a top-level filter, you HAVE to make sure that the context in which it works is the correct one. Having removed the guarding condition you are in muddy waters now... So, be sure to TEST THROUGHLY.

Best
D

Re: MAX Value on multiple dimensions and dynamic date

vincenzio — Fri, 17 Jul 2020 19:27:26 GMT

Anonymous

Hmm, I hear what you are saying. Guess my weekend is going to be studying DAX statements and testing out the different formulas in a simple version of my report. Thanks for the warning though.
Regards,

Vincent