How to show duplication that column is a duplication in a matrix table with state text

[annie_liu]

 I need help with a formula that can tell me that a particular date compare to another date ..that the STATE of the project are duplicated.

When I put it into PowerBI it shows like soo..using these fields

But I want it like this

[Anonymous]

Hi @annie_liu 

Assume that there is only one state on each date and there are only two dates in the matrix, you can try the following measure:

Result = 
var firstState = MAXX(TOPN(1, 'Table', 'Table'[Version Date], ASC), 'Table'[RPlan State])
var lastState = MAXX(TOPN(1, 'Table', 'Table'[Version Date], DESC), 'Table'[RPlan State])
return
IF(ISFILTERED('Table'[Version Date]),
MAX('Table'[RPlan State]),
IF(firstState=lastState, "Duplication", "Not Duplication"))

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[annie_liu]

This is great! but what happens if one of the two dates don't have a State ..what would be the formula for that?

[Anonymous · Accepted solution]

@annie_liu 

You may try this measure

Result = 
var firstState = MAXX(TOPN(1, 'Table', 'Table'[Version Date], ASC), 'Table'[RPlan State])
var lastState = MAXX(TOPN(1, 'Table', 'Table'[Version Date], DESC), 'Table'[RPlan State])
return
IF(ISFILTERED('Table'[Version Date]),
MAX('Table'[RPlan State]),
IF(COUNTROWS('Table')<2, "Not Duplication",
IF(firstState=lastState, "Duplication", "Not Duplication")))

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...