Text Comparision from multiple rows in the same column

[MStaford]

Hi Everyone,

Thankyou in advance for pardoning me for asking this questions and helping me with the solution.

Situation - I am working on a solution to idenfity the change in text between two entries of description added in two different dates for the same product name/ID.

These product ID and names are not in sequence and the dates could be three to six months apart. Also, the discriptions can vary from a single sentence to a para. The defference needs to be calculated between the latest two dates however, if needed, the solution should expand to all the dates.

I would prefer to apply this solution as a conditional format in a matrix table using ProductID's in Row, Date in Column and Description (First) as the value.

Here is the visual

Product-ID	Date	Descriptions
abc-123	01/2/2024	bla bla bal b
abc-123	01/6/2024	bla bla bal bl
abc-123	01/12/2024	bla bla bal bla
xyz-626	01/2/2024	yaba daba d
xyz-626	01/6/2024	yaba daba do
xyz-626	01/12/2024	yaba daba doo

I have tried to work with few solution but they are not giving great outcome plus i consider myself novice so not good at creating measures.

here is on measure i worked on
TxtDiff =

VAR CurrentItem = SELECTEDVALUE (sample-table'[Product-ID])

VAR CurrentDate = SELECTEDVALUE (sample-table'[Date])

var PrevText = CALCULATE (

MAX(sample-table'[Description]),

FILTER('sample-table', sample-table'[Product-ID] = CurrentItem && sample-table'[Date] < CurrentDate)
)
VAR CurrentText =SELECTEDVALUE (sample-table'[Description])

Return

IF(NOT(ISBLANK(PrevText)) && PrevText <>CurrentText,
"text are different",
"texts are the same"
)

the outcome is always coming up as "texts are the same" 😣

Thankyou in advance for your support 😊

[SamWiseOwl · Accepted solution]

Hi @MStaford 

Your max is returning the biggest text NOT the last text by date.

I've used top to return the relative last date for the current date.

So each date is comparing to the one before etc

Colour TxtDiff = 

VAR CurrentItem = SELECTEDVALUE('sample-table'[Product-ID])

VAR CurrentDate = SELECTEDVALUE ('sample-table'[Date])

VAR CurrentText =SELECTEDVALUE ('sample-table'[Descriptions])


var PrevText = CALCULATE (
maxx(
TOPN( 1,
        FILTER(all('sample-table'), 'sample-table'[Product-ID] = CurrentItem && 'sample-table'[Date] < CurrentDate),'sample-table'[Date],DESC)
,'sample-table'[Descriptions]
))

var final =
IF(NOT(ISBLANK(PrevText)) && PrevText <> CurrentText,
"#FF000050",
"#FFFFFF"
)

RETURN final

[MStaford]

Hi @SamWiseOwl and @v-zhengdxu-msft,
Thankyou for your posts and solutions. Please accept my apologies for delay in responding.
In desperation, I came across another solution which worked perfectly and I have applied it to my report.
I greatly appreciate your support and will be taking in the learnings from your suggessions.
Much appreciated your support and paitence.
Cheers

[v-zhengdxu-msft · Accepted solution]

Hi @MStaford 

Please try this measure:

TxtDiff =
VAR _currentDate =
    MAX ( 'Table'[Date] )
VAR _currentPD =
    SELECTEDVALUE ( 'Table'[Product-ID] )
VAR _previousdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] < _currentDate
                && 'Table'[Product-ID] = _currentPD
        )
    )
VAR _previousDescription =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[Descriptions] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Product-ID] = _currentPD
                && 'Table'[Date] = _previousdate
        )
    )
RETURN
    IF (
        NOT ISBLANK ( _previousDescription )
            && _previousDescription <> SELECTEDVALUE ( 'Table'[Descriptions] ),
        "text are different",
        "texts are the same"
    )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SamWiseOwl · Accepted solution]

Hi @MStaford 

Your max is returning the biggest text NOT the last text by date.

I've used top to return the relative last date for the current date.

So each date is comparing to the one before etc

Colour TxtDiff = 

VAR CurrentItem = SELECTEDVALUE('sample-table'[Product-ID])

VAR CurrentDate = SELECTEDVALUE ('sample-table'[Date])

VAR CurrentText =SELECTEDVALUE ('sample-table'[Descriptions])


var PrevText = CALCULATE (
maxx(
TOPN( 1,
        FILTER(all('sample-table'), 'sample-table'[Product-ID] = CurrentItem && 'sample-table'[Date] < CurrentDate),'sample-table'[Date],DESC)
,'sample-table'[Descriptions]
))

var final =
IF(NOT(ISBLANK(PrevText)) && PrevText <> CurrentText,
"#FF000050",
"#FFFFFF"
)

RETURN final