Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Maalst
Regular Visitor

Help on building a measure

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.

 

Maalst_0-1678777256026.pngMaalst_1-1678777256028.png

 

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

 

thanks in advance for your help

 

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

View solution in original post

10 REPLIES 10
tamerj1
Super User
Super User

Hi @Maalst 
Pleas try

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

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

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

 

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

@Maalst 
Please clarify further perhaps with some screenshots

Hi

 

See below the screen shot:

Maalst_0-1678790465689.png

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

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

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.

Maalst_0-1678798483987.png

 

 

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

Hello Tamerj1

 

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

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.