DAX mesures for compare text and dates in rows in one table with index

[dariaglb]

Hi

I have index's table where one sujbect has 3 rows with data, which need to compare with this logic:

if dates in columne "Date" "05-001-A" > "05-001-B"

then need to compare with date in 05-001 - 

else compare dates in 05-001-B and 05-001.

and second DAX mesure -

need to compare text in columne "Text" between 05-001 and 05-001-B

for result of both mesure - i need to get is 0 or 1 for color background of incorrect rows in 05-001

initial table:

subject	index	Date	Text
05-001	1	04.06.2024	Text 1
05-001-A	2	12.05.2024
05-001-B	3	05.06.2024	Text 2
06-001	4	12.03.2024	Text 3
06-001-A	5	12.03.2024
06-001-B	6	10.03.2024	Text 3

find mistakes - incorrect:

subject	index	Date	Text
05-001	1	04.06.2024	Text 1
05-001-A	2	12.05.2024
05-001-B	3	05.06.2024	Text 2
06-001	4	12.03.2024	Text 3
06-001-A	5	12.03.2024
06-001-B	6	10.03.2024	Text 3

[Anonymous · Accepted solution]

Thanks for the reply from @bhanu_gautam , please allow me to provide another insight:
Hi  @dariaglb ,

Here are the steps you can follow：

1. Create calculated column.

IF =
IF(
MOD('Table'[index],3)=0,1,0)

Group =
SUMX(
    FILTER(ALL('Table'),
    'Table'[index]>=EARLIER('Table'[index])),[IF])

Index_Group =
RANKX(
    FILTER(ALL('Table'),
    'Table'[Group]=EARLIER('Table'[Group])),[index],,ASC)

2. Create measure.

Measure1 =
var _index1=
MINX(FILTER(ALL('Table'),'Table'[Group]=MAX('Table'[Group])&&'Table'[Index_Group]=1),[Date])
var _index2=
MINX(FILTER(ALL('Table'),'Table'[Group]=MAX('Table'[Group])&&'Table'[Index_Group]=2),[Date])
var _index3=
MINX(FILTER(ALL('Table'),'Table'[Group]=MAX('Table'[Group])&&'Table'[Index_Group]=3),[Date])
var _su1=
MAXX(FILTER(ALL('Table'),'Table'[Group]=MAX('Table'[Group])&&'Table'[Index_Group]=1),[subject])
var _su2=
MAXX(FILTER(ALL('Table'),'Table'[Group]=MAX('Table'[Group])&&'Table'[Index_Group]=2),[subject])
var _su3=
MAXX(FILTER(ALL('Table'),'Table'[Group]=MAX('Table'[Group])&&'Table'[Index_Group]=3),[subject])
return
IF(
    _index2>_index3,
        IF(
        _index2>_index1,_su2,_su1),
    IF(
        _index3>_index1,_su3,_su1))

Measure2 =
var _text1=
MAXX(FILTER(ALL('Table'),'Table'[Group]=MAX('Table'[Group])&&'Table'[Index_Group]=1),[Text])
var _text3=
MAXX(FILTER(ALL('Table'),'Table'[Group]=MAX('Table'[Group])&&'Table'[Index_Group]=3),[Text])
return
IF(
_text1<>_text3&&MAX('Table'[index])=1,"red")

3. Select [Text] – Conditional formatting – Background color.

4. Result:

Best Regards,

Liu Yang

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

[bhanu_gautam]

@dariaglb , I have tried to create two measure for comparsion on your sample data and one measure to combine the outcome of two measure I am attaching PBIX file here but in the combined measure currently in sample data I am not getting one for any row you can check and update condition as per your requirement

[dariaglb]

Hi, thx for your solution,🙃
but subjects maybe several (06-001, 07-002, 05-002) and for it maybe need to use index of group of 3 rows? how do You think? i dont' know how to implement this 🤔