Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a table with historic data, some of the values were updated which means that if we look at it on a table there will be duplicate data. What I would like to do is that given a date range, the data displayed will be the latest version according to the time period.
Pd: date format DD/MM/YYYY
So for example, we have Customer 1, the payments recorded are all sequential until we get to payment 3. Here we have a duplicate. Both on March 5th but with a log date where the payment was created on February 1st but then updated on March 6th where the status was changed to paid as it was charged on March 5th. (The same situation for Customer 2 with payment 1).
So here when I filter the date of the payment, I get both results instead of just the updated data (based on the log date).
If I could filter this by the payment date, I would need it to only show the updated data based on the most recent log date for the specified dynamic filter below (year/month):
I used this relation for the date filter.
Now, the thing is, I know this can be done with columns but this will be static. I need the results to be dynamic so that if the payment date filter is adjusted, then the results displayed will always be the most recent based on the log date.
I have tried many methods but none seem to work, I would really appreciate any help.
Solved! Go to Solution.
After a long struggle trying to solve this, I was able to find the solution on a StackOverflow post from Feb 1st 2018.
R. Sanchez's solution is the one that worked for me and fits perfectly the situation described on this post.
https://stackoverflow.com/questions/48564840/power-bi-dax-compute-latest-value-for-filtered-rows
Create a filtering measure similar to something like this:
__Show = VAR __table = SUMMARIZE(ALL(Table5),[Level],"__Min",MIN([Price])) VAR __level = MAX([Level]) VAR __price = MAX([Price]) RETURN IF(__price = MAXX(FILTER(__table,[Level] = __level),[__Min]),1,0)
For a different problem, but same concept. You would use something like MAX([LogDate]) instead of MIN([Price]).
I implemented the measure but I have a inconvenient:
__Show = VAR __table = SUMMARIZE(ALL(CustomerPayments);CustomerPayments[PaymentDay];"__Max";MAX(CustomerPayments[LogDate])) VAR __level = MAX(CustomerPayments[PaymentDay]) VAR __logdate = MAX(CustomerPayments[LogDate]) RETURN IF(__logdate = MAXX(FILTER(__table;CustomerPayments[PaymentDay] = __level);[__Max]);1;0)
Keeping the data set the filter works.
But if in the data set registers new customers who pay in February 2019. The measure filters those with the highest LogDate. In this case excluding "Customer 4" because the maximum LogDate is "6/3/2019" and the LogDate of "Customer 4" is "1/2/2019". This customer must be included since he pays in the mentioned period.
I tried several ways, but I don't know how to perform the IF condition with a list.
After a long struggle trying to solve this, I was able to find the solution on a StackOverflow post from Feb 1st 2018.
R. Sanchez's solution is the one that worked for me and fits perfectly the situation described on this post.
https://stackoverflow.com/questions/48564840/power-bi-dax-compute-latest-value-for-filtered-rows
User | Count |
---|---|
83 | |
69 | |
68 | |
65 | |
54 |
User | Count |
---|---|
93 | |
93 | |
91 | |
76 | |
69 |