cancel
Showing results for
Did you mean:

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.

Helper I

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

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

1 ACCEPTED SOLUTION
Community Support

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!

4 REPLIES 4
Community Support

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!

Helper I

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

Community Support

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!

Super User

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors