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 the...
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!

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!