Table visualization not showing measure from one table when linked to another table with no match

[willayd]

I have a model that links orders to deliveries. For the sake of sample data, we can reproduce this model using the following DAX table constructors:

order = DATATABLE("order_number", INTEGER, "order_date", DATETIME, "order_quantity", INTEGER, {{100, "2020-01-01", 50}, {101, "2020-01-01", 75}})

delivery = DATATABLE("delivery_number", INTEGER, "order_number", INTEGER, "delivery_date", DATETIME, "delivery_quantity", INTEGER, {{1, 100, "2020-01-02", 50}})

So note that we have two orders but only one has actually been delivered. The model links these tables together on the order_number field

When building a table Power BI will generate this by default:

This is missing the order that doesn't have a delivery associated with it. To fix that, I am right clicking on the Values in the table visualization and selecting "Show items with no data", which produces the following table:

While this is closer, it is missing the order_quantity for order_number 101 in the table. What I am really hoping to see is the following:

order_number	order_date	order_quantity	delivery_number
100	2020-01-01	50	1
101	2020-01-01	75
Total		125

Is Power BI functioning as designed here, and if so is there some extra setting I need to check to have the order_quantity show for the order without a delivery?

[Anonymous]

Hi @willayd ,

Sorry for my late reply. You just need to set order_quantity and delivert_number as Sum options like this:

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[AllisonKennedy]

@willayd
This is due to the CROSS FILTER direction of your relationship.

When working with multiple tables, you cannot do a 'value' or 'summarization' on a column that is not on the many side of the relationship.

Do you have the order_quantity set to 'SUM'? Try clicking the down arrow next to order_quantity in the table and set it to 'don't summarize'
This should solve your problem.

Other way to achieve this if you want SUM, is to create a new calculated COLUMN in the delivery table:
Order_Quantity = RELATED(order[order_quantity])

Then use the Order_Quantity from the delivery table in your report.

[willayd]

Thanks @AllisonKennedy ! Your first suggestion of removing the aggregation does work, though with the downside of removing the total. The second suggestion doesn't seem to work at all - any thoughts on what may be missing there?

[AllisonKennedy]

When you say the second suggestion doesn't work at all - how far did you get? Were you able to create the calculated column?

[willayd]

Yep I was able to create it - it just produces the same results as shown in the original screenshot.

[AllisonKennedy]

@willayd 

Sorry, I didn't read your sample data carefully enough. Since you don't have all orders in the delivery table and because of the cross filter direction of your relationship, this won't work. See the attached sample file for a solution using an Orders Dimension table, which creates a distinct list of ALL order numbers. Use that for your visualizations, still set to show items with no data, and make sure to set the summarization to SUM on the order qty and Don't Summarize on the Delivery Number.

[willayd]

@AllisonKennedy thanks a ton for the suggestions! Unfortunately the problem still persists in the file that you shared. Note how the order_quantity is blank for order number 101 below: