Help on building a measure

[Maalst]

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

[tamerj1]

Hi @Maalst 
Pleas try

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

[Maalst]

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... 😬

[tamerj1]

@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
    )
)

[Maalst]

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

[tamerj1]

@Maalst 
Please clarify further perhaps with some screenshots

[Maalst]

Hi

See below the screen shot:

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

[tamerj1]

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

[Maalst]

Hi

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.

[tamerj1 · Accepted solution]

@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
    )
)

[Maalst]

Hello Tamerj1

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