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/

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.