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

Question

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?

allisonkennedy · Answer

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 · Answer

Thanks&nbsp;AllisonKennedy&nbsp;! 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 · Answer

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 · Answer

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

allisonkennedy · Answer

willayd&nbsp;
&nbsp;
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.
&nbsp;
&nbsp;

Forum Discussion

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

7 Replies

Related content

How to show multiple metrics/measures in a visual?

Visual is not showing all data

Show measure name in Matrix Visual

Adjust this measure to show totals

Measure showing different value in Table vs Card visual