Measure to display value based on amount conditions

[kitti]

Hello Experts,

Could you help me design a DAX measure for displaying value based on conditions like:

(By selected date)

The measure will display "GREEN" if:

  - RESULT is "pass"

The measure will display "RED" if:

  - RESULT is "fail" and is the first time (from the selected dated).

  - RESULT is "fail" after continuously 9 times of pass.

The measure will display "BLUE" if:

  - RESULT is "fail" before continuously 9 times of pass.

For example from day 1 - 15, if day 1 was pass then the measure showed GREEN, after that day 3 was fail then showed RED (first time failed) and then had a continuous pass (GREEN) until day 13 was fail (RED, after continuously day 4-12 passes). Lastly, day 14 was pass then it was GREEN but day 15 was fail so the measure displayed BLUE.

The data looks like this below:   (MEASURE_COLOUR is the measure I want)

PARAM_TEST	MEASUREMENT_DATE	RESULT	MEASURE_COLOUR
AB	1/10/2022	pass	GREEN
AB	2/10/2022	pass	GREEN
AB	3/10/2022	fail	RED
AB	4/10/2022	pass	GREEN
AB	5/10/2022	pass	GREEN
AB	6/10/2022	pass	GREEN
AB	7/10/2022	pass	GREEN
AB	8/10/2022	pass	GREEN
AB	9/10/2022	pass	GREEN
AB	10/10/2022	pass	GREEN
AB	11/10/2022	pass	GREEN
AB	12/10/2022	pass	GREEN
AB	13/10/2022	fail	RED
AB	14/10/2022	pass	GREEN
AB	15/10/2022	fail	BLUE

I truly appreciate any suggestions

KL

[kitti]

This is what I just tried but not correct just yet. Could anyone help

Measure =
VAR first_fail_date = CALCULATE( MIN(Sheet1[MEASUREMENT_DATE]), FILTER(Sheet1,Sheet1[RESULT]="fail"))
VAR count_fail_date = CALCULATE( COUNT(Sheet1[MEASUREMENT_DATE]), FILTER(Sheet1,Sheet1[RESULT]="fail"))

return
IF( SELECTEDVALUE(Sheet1[MEASURE_DATE]) = first_fail_date || count_fail_date > 9 , "RED", IF(count_fail_date < 9, "BLUE","GREEN"))

[DimaMD]

Hello @kitti  try 

Measure = 
VAR maxdata =CALCULATE( MAX('Таблиця'[MEASUREMENT_DATE]), ALL('Таблиця'))
return
IF( maxdata = MAX('Таблиця'[MEASUREMENT_DATE]), "blue", IF( SELECTEDVALUE('Таблиця'[RESULT]) = "pass", "green","red"))

[kitti]

@DimaMD  Thank you for your reply.

But, I didn't see the conditions about this:

The measure will display "GREEN" if:

  - RESULT is "pass"

The measure will display "RED" if:

  - RESULT is "fail" and is the first time (from the selected dated).

  - RESULT is "fail" after continuously 9 times of pass.

The measure will display "BLUE" if:

  - RESULT is "fail" before continuously 9 times of pass.

P.S. The actual data now has more than 3 months.

[DimaMD]

@kitti Hi, Can you provide more detailed data, for example, for 2 months?

[kitti]

@DimaMD 

This is the sample:

PARAM_TEST	MEASUREMENT_DATE	RESULT
AB	1/10/2022	pass
AB	2/10/2022	pass
AB	3/10/2022	fail
AB	4/10/2022	pass
AB	5/10/2022	pass
AB	6/10/2022	pass
AB	7/10/2022	pass
AB	8/10/2022	pass
AB	9/10/2022	pass
AB	10/10/2022	pass
AB	11/10/2022	pass
AB	12/10/2022	pass
AB	13/10/2022	fail
AB	14/10/2022	pass
AB	15/10/2022	fail
AB	16/10/2022	fail
AB	17/10/2022	pass
AB	18/10/2022	fail
AB	19/10/2022	pass
AB	20/10/2022	fail
AB	21/10/2022	pass
AB	22/10/2022	pass
AB	23/10/2022	pass
AB	24/10/2022	pass
AB	25/10/2022	pass
AB	26/10/2022	pass
ACB	17/10/2022	pass
ACB	18/10/2022	pass
ACB	24/10/2022	pass
ACB	25/10/2022	pass
ACB	27/10/2022	pass
ACB	28/10/2022	fail
ACB	30/10/2022	pass
ACB	31/10/2022	pass
ACB	1/11/2022	pass
ACB	2/11/2022	pass
ADB	2/11/2022	pass
ADB	3/11/2022	pass
ADB	4/11/2022	pass
ADB	5/11/2022	fail
ADB	6/11/2022	pass
ADB	7/11/2022	fail
ADB	8/11/2022	pass
ADB	9/11/2022	pass
ADB	10/11/2022	pass
ADB	11/11/2022	pass
ADB	12/11/2022	pass
ADB	13/11/2022	pass
ADB	14/11/2022	pass
ADB	15/11/2022	pass
ADB	16/11/2022	pass
ADB	17/11/2022	pass
ADB	18/11/2022	pass
ADB	19/11/2022	pass
ADB	20/11/2022	pass
ADB	21/11/2022	fail
ADB	22/11/2022	pass
ADB	23/11/2022	pass
ADB	24/11/2022	pass

Thank you very much

KL

[FreemanZ]

hi @kitti 

try to add a column with this:

Tag = 
VAR _date = [Date]
VAR _test = [TEST]
VAR _list =
CALCULATETABLE(
    data,
    ALLEXCEPT(data, data[Date],data[TEST]),
    data[Date]<_date+9,
    data[Date]>=_date+1
)
VAR _list2 =
CALCULATETABLE(
    DISTINCT(data[RESULT]),
    ALLEXCEPT(data, data[Date],data[TEST]),
    data[Date]<_date+9,
    data[Date]>=_date+1
)
RETURN
IF(
    [RESULT] = "PASS",
    "GREEN",
    IF(        
        NOT "Fail" in _list2 && COUNTROWS(_list) = 8,
        "BLUE",
        "RED"
    )
)

Hope it get what you expect:

p.s. if you need further suggestion on this topic, please consider @me. 

[kitti]

@FreemanZ Thank you very much for your help. I just tried it, however, it seems hangs as it's being stuck at Working on it even though I already tested with 1-month data.

The table now has around 300k rows (3-month data) indeed. Can we fine tune the DAX?

Thank you,

KL