Forum Discussion

jimpatel's avatar
jimpatel
Icon for Post Patron rankPost Patron
2 years ago
Solved

Dax formula

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 ...
  • amustafa's avatar
    amustafa
    2 years ago

    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's avatar
amustafa
Icon for Solution Sage rankSolution Sage
2 years ago

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