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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
annie_liu
Helper I
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

annie_liu_1-1707495919063.png

annie_liu_2-1707495942280.png

 

But I want it like this

annie_liu_3-1707495997358.png

 

 

 

1 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!

View solution in original post

4 REPLIES 4
v-jingzhan-msft
Community Support
Community Support

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"))

vjingzhanmsft_0-1707719453900.png

 

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

 

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

@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
Super User
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.

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors