Exclude group of unique ID in count measure that contain a certain value

[KvO88]

I would like to know what the count of Work Orders is that do not contain "Parts" (Line Type). So exclude any work order that at least contain 1 line with Line Type "Parts". 

See example of the table below.

Work Order #    Line Type

WO-00673116	Labor
WO-00673116	Parts
WO-00673116	Expenses
WO-00672716	Labor
WO-00672701	Labor
WO-00672404	Labor
WO-00672404	Parts
WO-00672404	Expenses
WO-00672302	Labor
WO-00672089	Labor
WO-00671319	Labor
WO-00671256	Labor
WO-00670986	Labor
WO-00670986	Parts
WO-00670986	Expenses
WO-00670463	Labor
WO-00670462	Labor

[v-binbinyu-msft · Accepted solution]

Hi @PijushRoy ,  many thanks for your very quick and effective replies.

Hi @KvO88 ,

Please try to create measure with below dax formual:

MEASURE =
VAR tmp =
    SUMMARIZE (
        'Table',
        'Table'[Work Order # ],
        "Concate Line Type", CONCATENATEX ( 'Table', [Line Type], "," )
    )
VAR tmp1 =
    FILTER ( tmp, NOT ( CONTAINSSTRING ( [Concate Line Type], "Parts" ) ) )
RETURN
    COUNTROWS ( tmp1 )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PijushRoy]

Hi @KvO88 

Please use the below DAX in measure. 

Measure 2 = CALCULATE(
    DISTINCTCOUNT('YourTable'[Work Order #]),
    'YourTable'[Line Type] <> "Parts")

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[KvO88]

@PijushRoy Thanks for thinking along. However, this only excludes the rows that do not have the line type "Parts". I only want to see the count of Work Orders that does not contain "Parts" at all. In the screenshot I only want to count WO-00591022, WO-00591040 and WO-00591219. I do not want include WO-00591263 and WO-00591438 because do do not only contain Labor. 

[AnalyticsWizard]

hi @KvO88 

1. Create a New Measure:

   • Go to the Modeling tab in Power BI.
   • Click on New Measure.
   • Name your measure (e.g., “Work Orders Without Parts”).

2. Write the DAX Formula:

   • Use the following DAX formula to count the work orders without the “Parts” line type:

     Work Orders Without Parts =
     CALCULATE(
         COUNTROWS(YourTableName),
         NOT(YourTableName[Line Type] = "Parts")
     )

     Replace 'YourTableName' with the actual name of your table.

3. Explanation:

   • The NOT function filters out rows where the “Line Type” is equal to “Parts”.
   • The measure counts the remaining work orders.

4. Display the Result:

   • Use the newly created measure “Work Orders Without Parts” in your visual to display the count.

[KvO88]

Hi @AnalyticsWizard Thanks for thinking along. This measure does not calculate distinct. Since each WO number can have multiple rows, it will not show the number of (distinct) WO's that do not contain Parts. See also my answer to PijushRoy. 

[PijushRoy]

Hi @KvO88 

Please try below DAX in measure

Measure 2 = CALCULATE(
    DISTINCTCOUNT('Table (2)'[Work Order #]),
    'Table (2)'[Line Type] <> "Parts")

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin