Need to filter result set in DAX Power BI

Question

I have following simple relationship:

I have created following visuals in Power BI:

I want to show Store Name, Orders (by Salesman selected in slicer) and Total Orders in that Store (ignoring Salesman selected in slicer). I have created two very simple measure (can be seen in above visual) and used in matrix visuals. Visual is showing All stores while I want to show only those stores where Salesman X (selected salesman in slicer) have orders i.e. I don't want Store B row.

while solving, I suspected that it is due to fact that visual is not cross filtering. I used crossfilter but it made no difference. data can be seen in below image:

Please guide. Thanks in advance.

Answer 1

Try to change [Total Orders] to this measure, but keep [Total Orders].

IF( ISBLANK([Orders Count]), BLANK(), [Total Orders])

Answer 2

By Adding VALUES('Order'[Store ID]) in measure solved the problem. complete measure definition is as follows:

Total Orders = CALCULATE(
count('Order'[Order ID]),
REMOVEFILTERS(Salesman[Salesman Name]),
VALUES('Order'[Store ID]))

This issues the problem but I could not understand how? Because VALUES bring only those stores where salesman has Order. But when salesman removed from the filter context by REMOVEFILTERS, then how come VALUES bring only stores where salesman have orders?

Answer 3

a) You intend to utilize Store.salesmanName from Store in a slicer, meaning whatever is selected from there, you intend that selection to be applied on Order to give you the Order.StoreName. So when X is selected only A and C are returned.

b) Once that selection happens, you intend DAX to return the total count of each Order.StoreName whether it has a corresponding Store.salesmanID in Order.salesmanID or not. In other words, in this layer of the analysis, you want the previous selection to remain applied in the outer loop but to be ignored in the inner loop.

To be able to do that, you can do this,

totalCount =
VAR _store =
    MAX ( 'Order'[storeID] ) //what is the max store ID
VAR _count =
    CALCULATE (
        COUNT ( 'Order'[SalesmanId] ),
        FILTER ( ALL ( 'Order' ), 'Order'[storeID] = _store ) //remove any filters and apply the value from above explicitly in the filter
    )
RETURN
    _count