Forum Discussion

annie_liu's avatar
annie_liu
Icon for Helper I rankHelper I
2 years ago
Solved

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

 

 

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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!

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    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-Forum/ba-p/963216

    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/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      annie_liu
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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!