CODEWITHSUNDEEP
powerbipowerquery
George
George

Reputation: 1

Creating a data flag in Power Bi

I am working with a detailed sales order dataset, whereby each line of data represents one line from the order. This means that if multiple products are present on an order, there will be multiple rows of data associated with the unique order number.

I am looking to create a 'flag' which will allow me to filter the dataset for all orders containing a particular product ID.

I am able to create this in excel using an intermediary flag (to identify the rows with the particular product), followed by a sumif against the order number and the intermediary flag.

Within Power Bi / Power Query, I'm able to identify the rows with the particular product ID using a simple IF statement, but struggling to find the right way to approach the DAX / logic for the second step.

I've mocked up the below table to illustrate with some simplified data. I am able to create the column 'Flag - incorrect' but need to get to 'Flag - correct'. In this example the product ID in focus would be 'A'.

Data example

Any input or ideas welcome, I may well be missing something obvious!

Best regards, George

Upvotes: 0

Views: 602

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88852

In Power Query reference the OrderItems table and create a new Orders table, that aggregates it to the Order grain. Add a column for Flag and use a MAX aggregate when grouping by OrderId, eg

    Source = OrderItems,
    #"Added Conditional Column" = Table.AddColumn(Source, "Custom", each if [ProductId] = "A" then 1 else null),
    #"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Flag"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"OrderId"}, {{"OrderItems", each Table.RowCount(_), Int64.Type}, {"Flag", each List.Max([Flag]), type nullable number}})
in
    #"Grouped Rows"

Upvotes: 0

Related Questions