Dax formula

[jimpatel]

Hi,

Thanks for looking at my post.

I have below code and can you any one please point me how to modify this code for below logic please?

If there is no "Green" at all then i need comment as "Please Check"

Any idea please?

measure 2 =

VAR _mat = MAX('Operation description'[Merged1])

VAR _laststep =

MAXX(

FILTER(

ALL('Operation description'),

'Operation description'[STATUS] = "GREEN"

&& 'Operation description'[Merged1] = _mat

),

'Operation description'[Column 3]

)

VAR _result =

CALCULATE(

COUNTROWS(

FILTER(

ALL('Operation description'),

'Operation description'[Column 3] = _laststep

&& 'Operation description'[Merged1] = _mat

)

)

)

RETURN

IF(

MAX('Operation description'[Column 3]) = _laststep,

_result,

BLANK()

)

[jimpatel]

Much appreciated your help

Will check the possible issues and will come back to you

Thanks again

[jimpatel]

Much appreciated again for your time.

Unfortunately its not giving any solution and its showing blank for red 😞

Thanks 

[amustafa]

Potential Issues and Solutions:

• Data Mismatch: Ensure that the data in 'Operation description'[STATUS] is correctly labeled as "RED". Sometimes issues with case sensitivity or extra spaces can cause mismatches.

• Filter Context: The use of ALL('Operation description') removes all filters from the 'Operation description' table. Verify that this is the intended behavior and that it's not causing unexpected results.

• Logic in _laststep: If there are no "GREEN" statuses in your data, _laststep will be set to _firstRed. Ensure that _firstRed is computed correctly.

• Checking Conditions: The final IF condition checks if the maximum of 'Operation description'[Column 3] is equal to _laststep. Verify that this condition is appropriate for your data and logic.

• Data Availability: Ensure that there are rows in your data that meet the criteria set in your measure. If no rows meet these conditions, the measure would naturally return BLANK.

[jimpatel]

Thanks a lot for your time and effort. It works perfectly fine. 

What if i need to do for one more logic please

1. If no green then take first red 

Thanks a lot and much appreciated your help

[amustafa · Accepted solution]

Glad to hear it worked. Try this updated DAX...

measure 2 =
VAR _mat = MAX('Operation description'[Merged1])
VAR _firstRed =
MINX(
FILTER(
ALL('Operation description'),
'Operation description'[STATUS] = "RED" &&
'Operation description'[Merged1] = _mat
),
'Operation description'[Column 3]
)
VAR _lastGreen =
MAXX(
FILTER(
ALL('Operation description'),
'Operation description'[STATUS] = "GREEN" &&
'Operation description'[Merged1] = _mat
),
'Operation description'[Column 3]
)
VAR _greenExists =
CALCULATE(
COUNTROWS(
FILTER(
ALL('Operation description'),
'Operation description'[STATUS] = "GREEN"
)
)
)
VAR _laststep = IF(_greenExists > 0, _lastGreen, _firstRed)
VAR _result =
CALCULATE(
COUNTROWS(
FILTER(
ALL('Operation description'),
'Operation description'[Column 3] = _laststep &&
'Operation description'[Merged1] = _mat
)
)
)
RETURN
IF(
MAX('Operation description'[Column 3]) = _laststep,
_result,
BLANK()
)

If I answered your question, please mark this thread as accepted.
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/

[amustafa]

Try this please....

measure 2 =
VAR _mat = MAX('Operation description'[Merged1])
VAR _laststep =
MAXX(
FILTER(
ALL('Operation description'),
'Operation description'[STATUS] = "GREEN" &&
'Operation description'[Merged1] = _mat
),
'Operation description'[Column 3]
)
VAR _result =
CALCULATE(
COUNTROWS(
FILTER(
ALL('Operation description'),
'Operation description'[Column 3] = _laststep &&
'Operation description'[Merged1] = _mat
)
)
)
VAR _greenExists =
CALCULATE(
COUNTROWS(
FILTER(
ALL('Operation description'),
'Operation description'[STATUS] = "GREEN"
)
)
)
RETURN
IF(
_greenExists = 0,
"Please Check",
IF(
MAX('Operation description'[Column 3]) = _laststep,
_result,
BLANK()
)
)