Create calculated measure from other table with filters

Question

I have four tables which I have tried related and unrelated:

Store (column "Store Number")
Calendar (column "Sales Date")
SKU (column "SKU Code")
Sales (columns "Store Number", "Sales Date", "SKU Code" and "Sales Quantity")

I have slicers on the Calendar and SKU tables

I need to list all stores with total "Sales Quantity" for each store and at the same time to limit the sales quantity to the two slicers mentioned above. Basically, I need to list these columns:

Store Number - from the Store table (no filtering from Slicers)
Store Name - from the Store table (no filtering from Slicers)
Total Quantity of Sales for the Store - calculated measure filtered by Calendar and SKU slicers

So my question is, what DAX required to create the calculated measure?

Please note I must list ALL stores regardless of whether they have sales in the stipulated period.

I've tried various DAX functions such as TREATAS, SUMMARIZE, ETC. I've tried with and without active relationships and with no relationships. The closest I've got is the code below, but it excludes stores with zero sales. I need all stores regardless of their sales.

Qty by Store = CALCULATE(
    sum(Sales[Sales Qty])
    ,USERELATIONSHIP(
        Sales[Store Number]
        ,Store[Store Number]
    )
)

The problem with the output I've managed is that stores without sales are excluded from the list. I need to have them included.

Can you share your relationship diagram and pictures of your current and desired situation? Have you tried changing the cross-filter direction to both in the relationship options for all relationships? — Jelle Hoekstra
Here is a link: 1drv.ms/u/s!AjQiTPc3xudmoaVpJGuayxfjrt3a-w?e=yiO6zn This is a sample pbix (sandbox) It has some sample data and the data model — RGI
Seems to work just fine by having both [Sales Qty] and [Store Name] to a table as long as you have active relationships in your model. Be sure to set the aggregation mode to sum (down arrow next to a column name in the fields pane of a visual). In case you want to show zeros as well, see the answer from @Alexis — Jelle Hoekstra

Nick Krasnov Nick Krasnov · Accepted Answer · 2019-08-16T13:41:10

Keep the relationship active, and change the DAX formula to

Qty by Store = 
    VAR res = sum(Sales[Sales Quantity])
RETURN IF (ISBLANK(res), 0, res)

There is no need for USERELATIONSHIP(). Relationship Store - Sales is already active. The reason why the number of stores changes in the table visual is because when there is no sale for a particular store Qty by store measure returns BLANK and those BLANKs get filtered out by the table.

Result:

Create calculated measure from other table with filters

2 Answers