Forum Discussion

Iceberg12's avatar
Iceberg12
Frequent Visitor
2 years ago
Solved

Need help to find missing Data

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   ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
bcdobbs
Community Champion
2 years ago

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.