topic Re: Measure to display value based on amount conditions in DAX Commands and Tips

Measure to display value based on amount conditions

kitti — Tue, 27 Dec 2022 09:56:18 GMT

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

Re: Measure to display value based on amount conditions

DimaMD — Tue, 27 Dec 2022 15:08:31 GMT

Hello kitti try

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

Re: Measure to display value based on amount conditions

kitti — Wed, 28 Dec 2022 01:20:40 GMT

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.

Re: Measure to display value based on amount conditions

kitti — Wed, 28 Dec 2022 04:27:00 GMT

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"))

Re: Measure to display value based on amount conditions

DimaMD — Wed, 28 Dec 2022 07:11:56 GMT

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

Re: Measure to display value based on amount conditions

kitti — Wed, 28 Dec 2022 07:54:13 GMT

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

Re: Measure to display value based on amount conditions

FreemanZ — Wed, 28 Dec 2022 14:34:11 GMT

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.

Re: Measure to display value based on amount conditions

kitti — Thu, 29 Dec 2022 02:45:05 GMT

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,