topic Re: Summarising in DAX Commands and Tips

Summarising

music43 — Wed, 03 Jul 2024 07:41:11 GMT

'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?

Re: Summarising

some_bih — Wed, 03 Jul 2024 09:30:17 GMT

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

Re: Summarising

music43 — Wed, 03 Jul 2024 09:36:18 GMT

Thanks some_bih

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

Re: Summarising

some_bih — Wed, 03 Jul 2024 10:34:25 GMT

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

Re: Summarising

Anonymous — Thu, 04 Jul 2024 02:04:39 GMT

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