Power BI - DAX query to remove duplicated rows in SUM calculation

Question

Sales value - duplicate removal

My scenario is this: SalesValue have been entered for multiple sessions namely Lunch, Breakfast, dinner which is grouped by SessionKey in numbers. The same SalesValue repeats at times for 2 or more sessions for a given production plan date, based on MenuKey, RawMaterialKey and IngSFKey.

I need to use DAX query in Power BI to remove duplicated SalesValue based on ProductionPlanDate and SessionKey for a particular MenuKey in a given date.

I have attached the screenshot of a sample value range of SalesValue containing duplicate values for the same date across different sessions for your reference. For example, rows 7 and 14 have the same ProductionPlanDate, SessionKey, MenuKey, and SalesValue.

You can certainly do this with a DAX calculated table, but it is probably better to do it in the query editor. Do you need to do it in DAX? — Alexis Olson

Brett Brett · Accepted Answer · 2018-07-21T10:21:02

So you have a table with one "Grain" and you want to change the "Grain" by using a subset of the columns. Specifically you want only rows with distinct columns ProductionPlanDate, SessionKey, MenuKey and SalesValue

To do this in a DAX query you would use

evaluate 
summarize
( 'table name'
, 'table name'[ProductionPlanDate]
, 'table name'[SessionKey]
, 'table name'[MenuKey] 
, 'table name'[SalesValue]
)

You could provide this to create a calculated table or provide it to each measure that needs to work with this coarser grained data set.

However as it seems you are in Power BI the more appropriate place to do this would be to create your coarser grained table using Power Query (via the Edit Queries section of Power BI).

This is better than doing it in DAX as DAX is more tuned to analytics where Power Query is tuned to data transformation - and you want to do data transformation.

You can either keep the table that you have now alongside the new modified or replace it accordingly.

option A will just change your incoming table to have the new coarse grain.
option B will keep your original table and have the new grained table alongside it. Note that this will mean any Power BI visuals that you have created will need to be "rewired" to use the new table.

To do the transform in Power Query, the steps for both options are

Go to the Edit Queries area on PowerBI
Select the columns that you want to create the new Grain (i.e. ProductionPlanDate, SessionKey, MenuKey and SalesValue) by holding ctrl and clicking the column headers of each column in turn.
Right click on the column header for one of the selected columns and select "Remove Duplicates"

If you want option B, simply first copy the existing table by using "Reference" then do the same thing as follows:

Find your existing table on the left Queries section, r-click and click Reference
Rename the new table something appropriate
Apply the transform steps to the new table as above
Click Close & Apply and rewire any existing visuals that you need to use the new table
If you find you don't need your old table you can R-click on it in Power Query again and uncheck "Enable Load" so that PowerBI will not see it anymore.

Power BI - DAX query to remove duplicated rows in SUM calculation

1 Answers