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 "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()
)
  • 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/

6 Replies

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

     

     

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

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

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

    Much appreciated again for your time.

     

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

     

    Thanks 

    • amustafa's avatar
      amustafa
      Icon for Solution Sage rankSolution Sage
      2 years ago

      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's avatar
    jimpatel
    Icon for Post Patron rankPost Patron
    2 years ago

    Much appreciated your help

     

    Will check the possible issues and will come back to you

     

    Thanks again