Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
birdie29
Helper II
Helper II

Unique lastest trasaction

Hi All

 

I'm trying to return a column that gives me the latest used ('Transaction Date')  'Reporting entity' by 'Project ID', however I'm having an issue when a 'Project ID' has two latest transactions on the same day using multiple 'Reporting Entities'. In this case I only want to see one reporting entity though it doesn't matter which one.

 

Capture3.PNG

 

In the example above the 'Latest Transaction?' column is just using an IF statement to determine if the 'Latest Transaction Date' matches the 'Transaction Date' but you can see it states 'yes' for two project ID's that are the same where I would only want it to say 'yes' to one row per unique project ID.

 

Is this possible at all?

 

Thank you

Chris

 

 

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

Hi @birdie29,

 

In your scenario, you can add a index column in Query Editor like below:

 

z1.PNG

 

Then back to the report, create a column to rank duplicate rows for each Project ID based on the index column, then create another column to return "Yes" for each Project ID when rank vale is 1.

 

Rank = RANKX(
         FILTER(
         ALLSELECTED(Table1),Table1[Project ID]=EARLIER(Table1[Project ID])
                   ),
         'Table1'[Index],
     ,1)

 

Latest Transaction? = IF('Table1'[Transaction Date]='Table1'[Latest Transaction Date] && 'Table1'[Rank]=1,"Yes","")

 

z2.PNG

 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-qiuyu-msft
Community Support
Community Support

Hi @birdie29,

 

In your scenario, you can add a index column in Query Editor like below:

 

z1.PNG

 

Then back to the report, create a column to rank duplicate rows for each Project ID based on the index column, then create another column to return "Yes" for each Project ID when rank vale is 1.

 

Rank = RANKX(
         FILTER(
         ALLSELECTED(Table1),Table1[Project ID]=EARLIER(Table1[Project ID])
                   ),
         'Table1'[Index],
     ,1)

 

Latest Transaction? = IF('Table1'[Transaction Date]='Table1'[Latest Transaction Date] && 'Table1'[Rank]=1,"Yes","")

 

z2.PNG

 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Assuming that the Project ID always falls in pairs next to eachother (like your example shows), try adding and Index column starting with zero and then add the following custom column:

 

try if Table1[ID]{[Index]}=Table1[ID]{[Index]-1} and [Latest Trans Date]=[Trans Date] and Table1[Latest Trans Date]{[Index]}=Table1[Latest Trans Date]{[Index]-1} then "Yes" else "" otherwise ""

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.