Forum Discussion
Summarising
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?
- Anonymous2 years ago
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 TeamIf 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
4 Replies
- some_bihCommunity Champion
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
- AnonymousNot applicable
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 TeamIf 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