Forum Discussion
Measure for counting ignoring context filters
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!
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
11 Replies
smpa01Community Champion
JacobCoops the equivalent dax is following
Measure = CALCULATE(COUNT('Table'[storeNumber]),ALLEXCEPT('Table','Table'[orderNumber]))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]))
CNENFRNLSELECT [OrderNr] , [LagerNr] , COUNT([LagerNr]) OVER (PARTITION BY [LagerNr]) [#DIST] FROM XXX WHERE [OrderNr] = '12345' GROUP BY [OrderNr] , [LagerNr]
