Display parent record when no child record exists

[TomSinAA]

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

[v-yanjiang-msft · Accepted solution]

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 _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@TomSinAA not sure what's going on. Can you share the pbix file, remove any sensitive information before sharing. 

[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)

[parry2k]

@TomSinAA Can you turn off show items with no data and then check?

[TomSinAA]

No rows are displayed when I remove the show items with no data 

[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]

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.

[TomMartens]

Hey @TomSinAA ,

you can enable the "Show item with no data" option:

Hopefully, this provides what you are looking for.

Regards,

Tom

[parry2k]

@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

[TomSinAA]

The 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.