topic Re: DAX Formula in DAX Commands and Tips

DAX Formula

Anonymous — Thu, 07 Nov 2024 12:44:03 GMT

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!

Re: DAX Formula

SachinNandanwar — Thu, 07 Nov 2024 12:41:43 GMT

Please post some sample data and expected output.

Re: DAX Formula

aduguid — Thu, 07 Nov 2024 13:34:55 GMT

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

Re: DAX Formula

Ankur04 — Fri, 08 Nov 2024 07:59:22 GMT

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,

Re: DAX Formula

Anonymous — Mon, 11 Nov 2024 09:10:40 GMT

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.