Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
[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
D
DAX has no concept of row numbers. You need to be more specific on what your sort criteriae are.
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
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?
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
[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
D
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
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |