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,
From same Table(TableA) group two columns(Column1 and Column2) and get previous row value(from Column3) in a new custom column in dax
Example
Column1 | Column2 | Column3 | Previous Row value |
A | Value1 | 01/02/2021 | |
A | Value1 | 02/02/2021 | 01/02/2021 |
A | Value1 | 03/02/2021 | 02/02/2021 |
A | Value2 | 05/02/2021 | |
A | Value2 | 05/02/2021 | 05/02/2021 |
A | Value2 | 04/02/2021 | 05/02/2021 |
B | Value1 | 01/02/2021 | |
B | Value1 | 01/02/2021 | 01/02/2021 |
This need to be done in dax code (Column3 is custom created in dax). Please suggest
Solved! Go to Solution.
Hi, @Gokulg
Please check the below for creating a new column.
Previous Row Value CC =
VAR currentindex = 'Table'[Index]
RETURN
CALCULATE (
MAX ( 'Table'[Column3] ),
FILTER (
'Table',
EARLIER ( 'Table'[Column1] ) = 'Table'[Column1]
&& EARLIER ( 'Table'[Column2] ) = 'Table'[Column2]
&& 'Table'[Index] = currentindex - 1
)
)
https://www.dropbox.com/s/t4yt3utpyj9t2u9/gokulg.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 @Jihwan_Kim
The reason why im adding Index in dax is,
Actually the TableA which i mentioned above is not same, the real table have more records and the rows are shuffled when imported(not same order as mentioned above). Also there is a Created Date column, we need to sort the data ASC.
When i try to sort the record from Query Editor like (Sort by Column1, sort by Column2 and sort by Created Date column) and then i created Index starts from 1 in Power Query editor. So when it comes to Visualization, the sort is not applied there. So when i try to sort in Visual by same order(Sort by Column1, sort by Column2 and sort by Created Date column) the Index value is not in sequence(not in order) like some of the Index values are shuffled. So the Previous Row Value CC is not proper.
So if we create Index in Dax after Sorting above columns, the Previous Row Value CC will be proper. Please give me your suggession on creating Index in dax or anyother ways to achieve this?
Hi @Jihwan_Kim ,
Thankyou for your help. One issue im facing to add Table index in dax, i used below code
Index =
RANKX(FILTER('TableA', EARLIER('TableA'[Column1]) = 'TableA'[Column1]
&& EARLIER('TableA'[Column2]) = 'TableA'[Column2]),
'TableA'[Column3],,DESC,Skip)
Here Column3 value is in date with time format (like 01/02/2021 17:55), so if both rows have same date with time im getting index as duplicate number
Example
Column3 Index
01/02/2021 17:54 1
01/02/2021 17:55 2
01/02/2021 17:55 2
01/02/2021 17:56 3
Please help me to get unique row index in dax
Hi, @Gokulg
Thank you for your feedback. I got an index column in Power Query Editor because I could not differentiate the below, and I could not understand the logic of the sequence. So I decided to choose the easiest way to create an index column which is doing it in Power Query Editor.
A | Value2 | 05/02/2021 |
B | Value1 | 01/02/2021 |
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, @Gokulg
Please check the below for creating a new column.
Previous Row Value CC =
VAR currentindex = 'Table'[Index]
RETURN
CALCULATE (
MAX ( 'Table'[Column3] ),
FILTER (
'Table',
EARLIER ( 'Table'[Column1] ) = 'Table'[Column1]
&& EARLIER ( 'Table'[Column2] ) = 'Table'[Column2]
&& 'Table'[Index] = currentindex - 1
)
)
https://www.dropbox.com/s/t4yt3utpyj9t2u9/gokulg.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.
Hello,
I know this is a bit old, but your solution works mostly great for something I'm working on, however, I'm wondering if there is a way to modify this so that instead of a blank, you actually get the previous row's value in the new column. So far I've been unable to do this.
ETA: Nevermind. I was able to get this by deleting the below line from the code:
&& EARLIER ( 'Table'[Column2] ) = 'Table'[Column2]
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 |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |