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[CRITICAL_STATUS_CODE] IN {"A", "B", "C", "D", "00", "01", "04"}
)
is registrered with a date in COMPLETED_DATE from my TBL_HISTORY tabel. I want to show the count that does not have a COMPLETED_DATE in TBL_History, so I only track "active" work orders. WORK_ORDER_NUMBERS is what is used to identify the work order between the tables. How can I achieve this?

Thank you!
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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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]
        )
    )
)
  • Ankur04's avatar
    Ankur04
    Icon for Resolver II rankResolver II
    1 year ago

    Hi Anonymous ,

     

    It will be really helpfull for community members to understand more about your requirement if you can share your table structure along with the modeling tab snap shot.

     

    Sometimes a problem can be solved with simple approach if it is explained well.

     

    please share some sample data and relationship details.

     

    Thanks,