Forum Discussion
Helper IVDisplay parent record when no child record exists
I have a parent table and a child table with related records. I created a table visual to display the parent record and count of IDs in the related child table. The visual does not display the parent record if there are no related child records.
So it displays the table visual as:
| Parent Table Field 1 | Count of child related records |
| A | 1 |
| B | 2 |
| C | 1 |
I want it to display as follows:
| Parent Table Field 1 | Count of child related records |
| A | 1 |
| B | 2 |
| C | 1 |
| D |
|
or
| Parent Table Field 1 | Count of child related records |
| A | 1 |
| B | 2 |
| C | 1 |
| D | 0 |
Hi TomSinAA ,
According to your description, here's my solution.
Sample:
Parent table:
Child table:
Create a measure:
Measure = CALCULATE ( COUNT ( ChildTable[Child] ), REMOVEFILTERS ( ChildTable[Child] ) )Put the measure in visual filter and select show items when the value is blank.
After apply filter, get the correct result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
12 Replies
- parry2k
Super User
TomSinAA you can use show item with no data and it will show everything. Show items with no data in Power BI - Power BI | Microsoft Learn
👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo
TomSinAAThe show items with no data works. See my visual with actual data:
Hazard data is in the parent table, the RequirementTypeNumber data is in the child table
If I want to see just the Hazards without a RequirementTypeNumber, I applied a filter:
And it displays Hazard: HZD-10705 as shown below:
However, HZD-10705 does have a RequirementTypeNumber in the child table as shown in the first screen.. I would have expected the filter to not display HZD-10705.
- TomMartens
Hey TomSinAA ,
you can enable the "Show item with no data" option:
Hopefully, this provides what you are looking for.
Regards,
Tom
- TomSinAA
Show items with no data works. Here is the visual with actual data:
Hazard data is the parent table, RequirementTypeNumber is in the child table
If I filter that visual for RequirementTypeNumber is blank (see below)
it displays Hazards that are not blank for RequirementTypeNumber
Hazard HZD-10705 has a record in the child table for RequirementTypeNumber. So when filtering for is blank I would expect HZD-10705 not to be listed.
- parry2k
When you are filtering the table, it is only filtering child table, not the parent.
there are few ways to solve it. Add a measure which is simple countrows of child table and then use it as a filter where this measure is blank.
- TomSinAA
Created the measure:
RiskRequirementRowCount = COUNTROWS(RiskRequirement)It does display the count without the filter:However, when I add the filter (rowcount is blank), it lists Hazards with RequirementTypeNumber child records
HZD-10705 still listed when filter is applied
- TomSinAA
No rows are displayed when I remove the show items with no data
- parry2k
Indeed make sense. I was trying to debug and guess the issue. Add +0 to countrows measure and the filter where measure = 0
- TomSinAA
I updated the measure to add 0, then filtered for =0, but it is still listing hazard with child records (e.g. HZD-10705)
v-yanjiang-msftCommunity Support
Hi TomSinAA ,
According to your description, here's my solution.
Sample:
Parent table:
Child table:
Create a measure:
Measure = CALCULATE ( COUNT ( ChildTable[Child] ), REMOVEFILTERS ( ChildTable[Child] ) )Put the measure in visual filter and select show items when the value is blank.
After apply filter, get the correct result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
