How to display text instead of blank values where there is lack of related data

[AxlCox]

I'm using two tables. The first is essentially parent records and the other is child records of those parents, so there's a many-to-one relationship built between them. Simplified, the data looks like this:

Parent Record #	Parent Name	Product Name
123456	Parent 1	Fruits
123457	Parent 2	Vegetables

Child Record #	Parent Record	Child Product
98765	12345	Orange
98766	12345	Cherry

What I'm trying to do is display custom text for rows in a table where a parent has no child records. So the table I have right now looks like this:

Parent	Parent Name	Parent Product	Child Record #	Child Product
123456	Parent 1	Fruits	98765	Orange
123457	Parnet 2	Vegetables
123456	Parent 1	Fruits	98766	Cherry

So these blank child fields are the result of there being no related child records. Instead of it being blank, I want to display text like "Out of Stock". I cant use conditional columns (assuming) because on the source tables, there are no blank records. I've tried a few different measurements but no luck.

Any suggestions are appreciated!

[v-echaithra]

Hi @AxlCox ,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

Best Regards,
Chaithra E.

[v-echaithra]

Hi @AxlCox ,

We would like to confirm if you've successfully resolved this issue or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.

[v-echaithra]

Hi @AxlCox ,

Thank you for sharing your update and confirming that you dont have any issue. i request you to please accept your own post as the solution, this will help other community members who might face a similar issue.

Thanks again for your contribution!

Regards,
Chaithra.

[Deku]

VAR hasNoChildren = ISEMPTY( children )
RETURN
IF( hasNoChildren, "out of stock", selectedvalue( child[child product] ) )

[Deku]

Child product = selectedvalue( child[child product], "out of stock")

This will list out of stock if a child has no children

[AxlCox]

It seems like this should work, but I got an error that says "Query has exceeded the available resources". I got this on a few other measurements I tried. 

It's strange because when I filter down my table (with your suggested measurement in place) to a single parent, that record now has hundreds of rows, looking like each child record is associated with it. There are only 2 child records actually associated with the filtered parent.

Note: I have a Many to One relationship set up between the child table and the parent table.

[andrewsommer]

If the child record # is on the parent table, instead of using a relationship I would look up that field as a custom column on the parent table.  Add a custom column to the Parent table that is   

Child Product = LOOKUPVALUE(ChildTable[Child Product],ChildTable[Child Record #],ParentTable[Child Record #])

Then add another column to the Parent table that is

Child Product Display = IF(ISBLANK(ParentTable[Child Product]),”Out of Stock”, ParentTable[Child Product])

[AxlCox]

No child records are on the parent table. Each child record does have the parent reference so I have a many to one relatioship set up. 

I'm thinking I may have to just do some aggregation and then make a conditional column for the text when no child is present...?

[AxlCox]

Update on this. I wound up just merging the tables. In the end it made everything I was trying to do easier, including these suggestions.