Measure for counting ignoring context filters

[JacobCoops]

Hello,

I want to (distinctly) count how many stores have participated in the delivery of an order. I want the measure to ignore the specific filtercontext for the calculations only and not for the visual itself. The model is a Direct Model on an Analysis Service using a Star Scheme. An example of the data:

In the above picture i want it to write 3 on every row whilst only showing the 3 stores in question. Using AllExcept, ALLSELECTED etc. either does the same as the picture or counts every single "Store No." in the dataset. What i want is to tag every "Order No." with the number of unique "Store No.".

In SQL i would do it like in the picture below, but i do not know how in DAX.

Any help would be appreciated!

[CNENFRNL]

SELECT [OrderNr] ,
       [LagerNr] ,
       COUNT([LagerNr]) OVER (PARTITION BY [LagerNr]) [#DIST]
FROM XXX
WHERE [OrderNr] = '12345'
GROUP BY [OrderNr] , [LagerNr]

[smpa01]

@JacobCoops  the equivalent dax is following

Measure = CALCULATE(COUNT('Table'[storeNumber]),ALLEXCEPT('Table','Table'[orderNumber]))

[JacobCoops]

Hi,

That return the following (there can be multiple rows per "Order No." and "Store No." combination which i eliminate using the group by in the SQL)

[smpa01]

@JacobCoops  can you try this

Measure = CALCULATE(DISTINCTCOUNT('Table'[storeNumber]),ALLEXCEPT('Table','Table'[orderNumber]))

[JacobCoops]

Hi,

This is where it gets strange. I would expect your suggestion to work, but i returns the following. Do you think my model i messed up?

[v-yanjiang-msft]

Hi @JacobCoops ,

I notice the Order No and Store No are not in the same table in your formula, is there Order No column in the Store table? You should use the same table in the formula, it will work fine.

Dilivered from stores# = CALCULATE(DISTINCTCOUNT('Sotre'[Store No]),ALLEXCEPT('Sotre','Sotre'[Order No]))

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JacobCoops]

Hi,

Correct, they are not in the same table. They are connected via. the fact where i join them on their respective keys.

I have tried to distinctcount on the keys in the fact table, but that is not yielding the correct results either.

[v-yanjiang-msft · Accepted solution]

Hi @JacobCoops ,

According to your description, Order No and Store No are in different tables, I create a sample, they have relationship by the Key column.

Here's my solution, create a measure.

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Store'[Store No] ),
    FILTER ( ALL ( 'Order' ), 'Order'[Order No] = MAX ( 'Order'[Order No] ) ),
    FILTER ( ALL ( 'Store' ), 'Store'[Key] = MAX ( 'Store'[Key] ) )
)

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@JacobCoops  can you please provide some sample data please?

[JacobCoops]

Hi, 

I can't upload files to this thread. Do you have any suggestions to how i can send it?

[smpa01]

@JacobCoops  can you please upload a pbix in g/1 drive or any other file hosting service and share the link here?