Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

DAX Formula

I want to create a measure/column that can filter if the following:  Timeliness_600 = CALCULATE(     COUNTROWS(TBL_WORK_ORDER),     TBL_WORK_ORDER[PRIMARY_TRADE] = "600",     TBL_WORK_ORDER[CRI...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

    Based on the description, please try the following methods:

    1.Create the sample table.

    2.Create the new column to filter the order.

    Filter Work Order = 
IF(
    TBL_WORK_ORDER[PRIMARY_TRADE] = 600 &&
    TBL_WORK_ORDER[CRITICAL_STATUS_CODE] IN {"A", "B", "C", "D", "00", "01", "04"} &&
    ISBLANK(RELATED('Table history'[COMPLETED_DATE])),
    1, 
    0
)

    3.Create the new measure to calculate the count.

    Count not completed date = 
CALCULATE(
    COUNTROWS(TBL_WORK_ORDER), TBL_WORK_ORDER[Filter Work Order] = 1)

    4.The result is shown below.

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

aduguid's avatar
aduguid
Icon for Memorable Member rankMemorable Member
1 year ago

Try this one

 

Timeliness_600_Active =
CALCULATE(
    COUNTROWS(TBL_WORK_ORDER),
    TBL_WORK_ORDER[PRIMARY_TRADE] = "600",
    TBL_WORK_ORDER[CRITICAL_STATUS_CODE] IN {"A", "B", "C", "D", "00", "01", "04"},
    NOT(
        TBL_WORK_ORDER[WORK_ORDER_NUMBERS] 
        IN 
        SELECTCOLUMNS(
            FILTER(
                TBL_HISTORY, 
                NOT(ISBLANK(TBL_HISTORY[COMPLETED_DATE]))
            ),
            "WORK_ORDER_NUMBERS", TBL_HISTORY[WORK_ORDER_NUMBERS]
        )
    )
)