Forum Discussion
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 |
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,
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.
4 Replies
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 👍🤠
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.
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.Thank you all for your kind support 🙂
