Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there,
I have table with Id, Date and Amount.
As you can see the OrderId repeats sometimes and what i need to find is latest occurance. For example, OrderId 10005 happens 3 times and i need to mark the last accourance. So ultimately in the same table i would like to get something like this:
OrderId | Date | Amount | Occurance | LastOccurance |
10001 | 1/5/2021 | 5 | 1 | Yes |
10002 | 1/6/2021 | 6 | 1 | Yes |
10003 | 1/7/2021 | 7 | 1 | No |
10003 | 1/8/2021 | 10 | 2 | Yes |
10004 | 1/9/2021 | 4 | 1 | Yes |
10005 | 1/10/2021 | 7 | 1 | No |
10005 | 1/11/2021 | 4 | 2 | No |
10005 | 1/12/2021 | 10 | 3 | Yes |
10006 | 1/13/2021 | 63 | 1 | Yes |
10007 | 1/14/2021 | 43 | 1 | Yes |
10008 | 1/15/2021 | 23 | 1 | Yes |
10009 | 1/16/2021 | 56 | 1 | No |
10009 | 1/17/2021 | 60 | 2 | Yes |
10010 | 1/18/2021 | 43 | 1 | Yes |
10011 | 1/19/2021 | 21 | 1 | Yes |
This will tell me if the value for that order is last or not.
How do i do this using Dax?
Solved! Go to Solution.
Hi @slav84
Create a calculated column:
Last occurence =
VAR lastDate_ = CALCULATE(MAX(Table1[Date]), ALLEXCEPT(Table1, Table1[OrderId]))
RETURN
IF(lastDate_ = Table1[Date], "Yes", "No")
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Hi, @slav84
Please check the below picture and the sample pbix file's link down below.
I tried to create a measure for a visualization.
All measures are in the sample pbix file.
https://www.dropbox.com/s/2uur383m43k2t18/slav84.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @slav84
Create a calculated column:
Last occurence =
VAR lastDate_ = CALCULATE(MAX(Table1[Date]), ALLEXCEPT(Table1, Table1[OrderId]))
RETURN
IF(lastDate_ = Table1[Date], "Yes", "No")
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |