DAX Formula

[Anonymous]

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!

[Anonymous · Accepted solution]

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.

[Ankur04]

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,

[aduguid]

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]
        )
    )
)

[SachinNandanwar]

Please post some sample data and expected output.