Forum Discussion

willayd's avatar
willayd
Icon for Advocate I rankAdvocate I
6 years ago

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

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,...
Need Help
AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
6 years ago
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's avatar
willayd
Icon for Advocate I rankAdvocate I
6 years ago

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's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    6 years ago
    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's avatar
      willayd
      Icon for Advocate I rankAdvocate I
      6 years ago

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

      • AllisonKennedy's avatar
        AllisonKennedy
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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.