topic Re: Measure for counting ignoring context filters in DAX Commands and Tips

Measure for counting ignoring context filters

JacobCoops — Tue, 14 Dec 2021 14:15:01 GMT

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!

Re: Measure for counting ignoring context filters

smpa01 — Tue, 14 Dec 2021 14:29:13 GMT

JacobCoops the equivalent dax is following

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

Re: Measure for counting ignoring context filters

JacobCoops — Tue, 14 Dec 2021 14:48:17 GMT

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)

Re: Measure for counting ignoring context filters

smpa01 — Tue, 14 Dec 2021 14:53:50 GMT

JacobCoops can you try this

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

Re: Measure for counting ignoring context filters

JacobCoops — Tue, 14 Dec 2021 15:12:05 GMT

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?

Re: Measure for counting ignoring context filters

smpa01 — Tue, 14 Dec 2021 15:15:12 GMT

JacobCoops can you please provide some sample data please?

Re: Measure for counting ignoring context filters

CNENFRNL — Tue, 14 Dec 2021 18:04:07 GMT

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

Re: Measure for counting ignoring context filters

v-yanjiang-msft — Fri, 17 Dec 2021 09:12:57 GMT

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.

Re: Measure for counting ignoring context filters

JacobCoops — Mon, 20 Dec 2021 08:11:21 GMT

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.

Re: Measure for counting ignoring context filters

JacobCoops — Mon, 20 Dec 2021 08:22:39 GMT

Hi,

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

Re: Measure for counting ignoring context filters

smpa01 — Mon, 20 Dec 2021 16:42:16 GMT

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

Re: Measure for counting ignoring context filters

v-yanjiang-msft — Fri, 31 Dec 2021 05:47:58 GMT

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.