Summarising

[music43]

Hi

'Sales' is my fact table with approx 5.5M rows and the granularity is at the order item level. An order can have anywhere from 1 to 9999 lines/rows.
The carriage/freight cost for each order is recorded on the item level and as such, if an order has more than one line/row, the value is repeated.
To allow me to SUM the unique carriage cost per transaction I want to group by the 'Sales'[Transaction ID].
Additionally I have these filters

•  'Sales'[Order Status] = "Invoiced" - To remove cancelled or incomplete orders.
•  'Sales'[Order Carriage (GBP)] <> 0 - To remove the 300K rows without a carriage charge.

This is what I have come up with so far and it works.

Carriage =
VAR __InvoicedCarriage =
    FILTER(
        'Sales',
        'Sales'[Order Status]="Invoiced" 
        && 'Sales'[Order Carriage (GBP)] <> 0
    )
VAR __GroupByOrderID =
    SUMMARIZE(
        __InvoicedCarriage,
        'Sales'[Transaction Id],
        'Sales'[Order Carriage (GBP)]
    )
VAR __Result =
    SUMX(
        __GroupByOrderID,
        'Sales'[Order Carriage (GBP)]
    )
RETURN
    __Result

My questions are as follows:

1) Is there a better (more compact/efficient) way to write my formula?

2) I thought it was bad practice to filter a fact table - How else could I do this?

[Anonymous · Accepted solution]

Hi, @music43 

Thanks for @some_bih reply. You can try the following measure(compact).

Carriage =
SUMX(
    SUMMARIZE(
        FILTER(
            'Sales',
            'Sales'[Order Status] = "Invoiced"
            && 'Sales'[Order Carriage (GBP)] <> 0
        ),
        'Sales'[Transaction Id],
        'Sales'[Order Carriage (GBP)]
    ),
    'Sales'[Order Carriage (GBP)]
)

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[some_bih]

Hi @music43 

ok for limitations of your model.

The performance / optimisation is complex task including using  Performance analyzer, DAX Studio to see and check queries.

One of option, to investigate best performance is eventually using GROUPBY, SUMMARIZE or pattern ADDCOLUMNS(SUMMARIZE

Check link as example

[some_bih]

Hi @music43 try in Power Query in pbi filter out your filter (or the best do not "import" at all) your 2 filters criteria

Sales'[Order Status]="Invoiced"

'Sales'[Order Carriage (GBP)] <>0

[music43]

Thanks @some_bih 

Unfortunately, I can't remove them in PQ as I need the data for other parts of the report.