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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
BBConsultancy
Regular Visitor

Calculated column showing the amount of lines per order

In my order lines table, I want a calculated column that shows the number of order lines of that order. So for each order line, I want to know how many order lines there are with a similar OrderID. One other filter that needs to be applied is that the LineNumber cannot be 0. Line number zero's need to be excluded, because those are order headers in my data.

1 ACCEPTED SOLUTION
sevenhills
Super User
Super User

It will be better you paste a sample of data (remove the key identifiers and a sample is good)

 

Let us say, assuming you have a table called TableOrder. order_id column is unique for each order and line_number is within each order and you can start with like below ... 

 

 

[Items per Order] =
var _currOrderID = TableOrder[order_id]
return
    COUNTX(
        filter(
            TableOrder,
            TableOrder[order_id] = _currOrderID && TableOrder[line_number] > 0
        ),
        TableOrder[line_number] -- you can use any primary key column of the TableOrder
    )

 

 

View solution in original post

3 REPLIES 3
v-tianyich-msft
Community Support
Community Support

Hi @BBConsultancy ,

 

I made simple samples and you can check the results below:

vtianyichmsft_0-1709259926232.png

Measure = CALCULATE(COUNT('Table'[ID]),ALLEXCEPT('Table','Table'[Category]))

 

An attachment for your reference. Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

sevenhills
Super User
Super User

It will be better you paste a sample of data (remove the key identifiers and a sample is good)

 

Let us say, assuming you have a table called TableOrder. order_id column is unique for each order and line_number is within each order and you can start with like below ... 

 

 

[Items per Order] =
var _currOrderID = TableOrder[order_id]
return
    COUNTX(
        filter(
            TableOrder,
            TableOrder[order_id] = _currOrderID && TableOrder[line_number] > 0
        ),
        TableOrder[line_number] -- you can use any primary key column of the TableOrder
    )

 

 

Thank you, this works perfectly!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors