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.

    11-11DAX formula to calculate count.pbix44 KB

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.

    11-11DAX formula to calculate count.pbix44 KB
  • aduguid's avatar
    aduguid
    Memorable 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
    Resolver 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,