Forum Discussion
Column repeating the first value encountered on duplicates value
Hello,
I am reaching out in the hope of finding some guidance for the following situation: I have a raw data that contains one line for each transaction. The transactions can belong to the same case, so a case number can appear multiple times. Nontheless, the performance of each instances of the case can differ. What I am looking for is a way to look at the performance of the first instance of the case and then to display the same value for all the following instances.
I hope the explanation is sufficiently clear.
Any tip would be appreciated.
Thank you!
Amalia
Anonymous , Assume you have case id, Transaction date and performance column
Then you can have a new column like
New column =
var _min = minx(filter(Table, Table[Case id] =earlier([Table[Case id]) ), Table[ Transaction date])
return
maxx(filter( Table, Table[Case id] =earlier([Table[Case id]) && Table[ Transaction date] =_min ) , Table[Performace])
Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s
3 Replies
- amitchandak
Super User
Anonymous , Assume you have case id, Transaction date and performance column
Then you can have a new column like
New column =
var _min = minx(filter(Table, Table[Case id] =earlier([Table[Case id]) ), Table[ Transaction date])
return
maxx(filter( Table, Table[Case id] =earlier([Table[Case id]) && Table[ Transaction date] =_min ) , Table[Performace])
Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s Hi Anonymous ,
I created some data:
Here are the steps you can follow：
If you can determine the chronological date in your data:
Create measure.
Date_Measure = var _groupmindate= MINX( FILTER(ALL('Table'),'Table'[case]=MAX('Table'[case])&&'Table'[instances]=MAX('Table'[instances])),[Date]) return MAXX( FILTER(ALL('Table'), 'Table'[case]=MAX('Table'[case])&&'Table'[instances]=MAX('Table'[instances])&&'Table'[Date]=_groupmindate),[performance])Result:
If you don't have chronological dates in your data, you can create an Index in Power Query to distinguish them:
In Power Query -- Add Column – Index Column – From 1.
Create measure.
Index_Measure = var _groupminindex= MINX( FILTER(ALL('Table'),'Table'[case]=MAX('Table'[case])&&'Table'[instances]=MAX('Table'[instances])),[Index]) return MAXX( FILTER(ALL('Table'), 'Table'[case]=MAX('Table'[case])&&'Table'[instances]=MAX('Table'[instances])&&'Table'[Index]=_groupminindex),[performance])Result：
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly