topic Re: Simple measure prolbem in Developer

Simple measure prolbem

Marcin82 — Fri, 23 Jun 2023 13:44:52 GMT

I got two tables. One Is OrderNumber (with distinct values) and second is Sales Order Items. I built a matrix with order number from OrderNumber and itemnumber from Sales Order Items. One order can have many items of course. At every row of the the matrix I just want to have a number of items for this order. I needed to remove filter from order item column so as it

CALCULATE(

COUNT(

Sales_order_item[item_id]),

ALL(Sales_order_item[item_id]
)
)
But the measure completely destroys the matrix. Now, under every order I see all possible items. Why is that???

Re: Simple measure prolbem

Dhairya — Mon, 26 Jun 2023 05:39:07 GMT

Hey @Marcin82
Can you please share your sample dataset and expected output so that I can help you with an accurate solution.

Re: Simple measure prolbem

Manoj_Nair — Mon, 26 Jun 2023 22:44:31 GMT

@Marcin82- Hi, try using ALLEXCEPT instead of ALL. The ALLEXCEPT function will keep all context filters except for those specified, so it will maintain the order context while calculating the item count.

ItemCount = CALCULATE( COUNT(Sales_order_item[item_id]), ALLEXCEPT(Sales_order_item, Sales_order_item[OrderNumber]) )

If this post helps to find solution would be happy if you could mark my post as a solution and give it a thumbs up

Best regards

Manoj Nair
Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/

Re: Simple measure prolbem

Marcin82 — Wed, 28 Jun 2023 21:31:26 GMT

Doesn't work. I still see all possible items under order. My measure works when orderNumber is from same table as item. If it comes from "higher level" table (one side realtion) it shows all items under order. SImple need huge problem in DAX, as always.

Re: Simple measure prolbem

Marcin82 — Wed, 28 Jun 2023 21:44:35 GMT

I would like to have only relevant items under order with count of all of them.
I achieved it when I add VALUES(OrderNo) from OrderItems table as a CALCULATE argument . But why I get such a mess without it? There is a filter in the context from OrderNumber (but from other table). It should still be kept. Measure only removes filter on items.