topic Re: Help on building a measure in DAX Commands and Tips

Help on building a measure

Maalst — Tue, 14 Mar 2023 07:02:19 GMT

Hello all

I’m not a DAX expert – not even a medium skilled user – so I’m looking for help. My table has the following data (order numbers, items and quantity shipped) 1 order can occur multiple times in the table (because an order can have multiple lines)

I’m looking for orders that have only 1 line and a shipped quantity of 1.

That value (hopefully it is the outcome of a measure) needs to show in a card-visual. I can get the result in a normal table:

Count of Order: Distinct count of order and on then a filter on Quantity =1
Sum of Qty: and also filter on Quantity =1

The table looks like: the outcome is highlighted in yellow.

But I want to use this in a card and only the number 228

thanks in advance for your help

Re: Help on building a measure

tamerj1 — Tue, 14 Mar 2023 08:02:35 GMT

Hi Maalst
Pleas try

Single Line Orders = COUNTROWS ( FILTER ( SUMMARIZE ( 'Table', 'Table'[Order], "@Lines", COUNTROWS ( 'Table' ) ), [@Lines] = 1 ) )

Re: Help on building a measure

Maalst — Tue, 14 Mar 2023 10:20:28 GMT

Thanks al lot Tamerj1

As said before Im a DAX beginner so what I did is copy your code in a measure and update file and table names.

I think the red text, indicates an error (no idea what that is doing) the outcome of this measure is not 228 so Im doing something wrong....

Single line single piexe = COUNTROWS(

filter (

SUMMARIZE(Z_orders_delivered_2022,Z_orders_delivered_2022[ORDER],"@lines", COUNTROWS(Z_orders_delivered_2022)),

[@lines] =1))

any idea?

Please dont spend too much time on it, I appreciate all the help I can get but also understand that beginner questions can be frustrating... 😬

Re: Help on building a measure

Maalst — Tue, 14 Mar 2023 10:25:02 GMT

extra info: the value the measure returns is the count of orders and with a filter on value 1.

so it does give me the lines with "sum_ship" = 1

Re: Help on building a measure

tamerj1 — Tue, 14 Mar 2023 10:32:27 GMT

Maalst
It shouldn't. Nothing wrong it should work. Just hit "Enter" or confirm the measure the error will go. If persists, please provide a screenshot of the error message.

Single line single piexe = COUNTROWS ( FILTER ( SUMMARIZE ( Z_orders_delivered_2022, Z_orders_delivered_2022[ORDER], "@lines", COUNTROWS ( Z_orders_delivered_2022 ) ), [@lines] = 1 ) )

Re: Help on building a measure

tamerj1 — Tue, 14 Mar 2023 10:34:35 GMT

Maalst
Please clarify further perhaps with some screenshots

Re: Help on building a measure

Maalst — Tue, 14 Mar 2023 10:42:32 GMT

See below the screen shot:

If I take the file SUM_SHIP is 1 away (from my table) than 228 changes to 307.

Re: Help on building a measure

tamerj1 — Tue, 14 Mar 2023 10:48:34 GMT

Maalst
What is SUM_SHIP? is it a measure? What is the DAX/Column?

Re: Help on building a measure

Maalst — Tue, 14 Mar 2023 12:54:51 GMT

Sum_ship is jsut a numeric field with shipped QTY (the word Sum might be misleading)

dont understand the question: What is the DAX/Column? Maybe the screenshot answers the question.

Re: Help on building a measure

tamerj1 — Tue, 14 Mar 2023 13:06:52 GMT

Maalst
Please try

Single line single piexe = COUNTROWS ( FILTER ( SUMMARIZE ( Z_orders_delivered_2022, Z_orders_delivered_2022[ORDER], "@CountSumOrders", COUNT ( Z_orders_delivered_2022[Z_orders_delivered_2022[SUM_ORDER] ), "@CountShipOrders", COUNT ( Z_orders_delivered_2022[Z_orders_delivered_2022[SUM_ORDER] ) ), [@CountSumOrders] = 1 && [@CountShipOrders] = 1 ) )

Re: Help on building a measure

Maalst — Tue, 14 Mar 2023 14:43:18 GMT

Hello Tamerj1

Thanks for all your help, I have a working measure now👍