Need help to find missing Data

[Iceberg12]

Hello everyone, 

I am new to PowerBI and I need your help with the following:

I have a table like this

Component	Missing
A	yes
A	yes
B	no
C	no
C	no
D	yes

I would like to have a table which show the missing components only as the following:

Components	Count of missing
A	2
D	1

This table should be blank, if there is no component missing.

Thank you for your help and support.
Best regards,

[Iceberg12]

Thank you all for your kind support 🙂

[Anonymous · Accepted solution]

Hi @Iceberg12 ,

Please try below steps:

1. below is my test table

Table:

2. create a measure with below dax formula

Count Of Missing =
VAR cur_component =
    SELECTEDVALUE ( 'Table'[Component] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Component] = cur_component && [Missing] = "yes" )
RETURN
    COUNTROWS ( tmp )

3. add a table visual with field and measure

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.

[bcdobbs]

I'd create a Compnents table with distinct list of components (use distinct function in power query).

Relate it to the missing table.

Then write a measure:

CountMissing =
CALCULATE (

COUNTROWS (MissingTable),

MissingTable[Missing] = "yes"

)

If you use that in a matrix it will only show values when there is a count.

[Daniel29195]

@Iceberg12 

step 1 : create a table visual

step 2 : drag and drop into the visual the column "  Component " 

step 3 : in the filter pane, drag to the section , filter on this visual , --> Missing 

step 4 : set your filter =  yes

this should work ..

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. I would appreciate hitting that kudos button 👍🤠