Custom Column based on advanced filter rules

[Matt0515]

Hello experts, 

I need to create a custom column based on a complicated set of rules.

I need the column to return "X" when a row meets the following requirements.

Im able to create a measure to achieve the record count, but need it to become a value in a row.

How can I write the M Code in power query to create the custom column?

FILTER (

        ALLSELECTED ( Current_Report_Raw_Data ),

        YEAR ( Current_Report_Raw_Data[SQL Date] ) = 2020

            && QUARTER  ( Current_Report_Raw_Data[SQL Date] ) = 1

            &&          ( Current_Report_Raw_Data[Opportunity Solution] = "X" || Current_Report_Raw_Data[Opportunity Solution] = "Y" )

            &&          ( Current_Report_Raw_Data[Sirius Stage] = "SQL")

            &&          ( Current_Report_Raw_Data[Opportunity Record Type] = "Y" || Current_Report_Raw_Data[Opportunity Record Type] = "Z")

            &&          ( Current_Report_Raw_Data[Crystallised Opp Booking Value (GBP)] > 1

            &&          ( Current_Report_Raw_Data[Crystallised Opp Booking Value (GBP)] > 5000 || NOT Current_Report_Raw_Data[Opportunity Sale Type] = "Upsell")

            && NOT      ( Current_Report_Raw_Data[Enterprise Sales Team] ) = "DLS"

            && NOT      ( Current_Report_Raw_Data[Account Name] = "PPD Global" || Current_Report_Raw_Data[Account Name] = "ABC")

            && NOT      ( Current_Report_Raw_Data[Main Product] = "123" || Current_Report_Raw_Data[Main Product] = "456")

            && NOT      ( Current_Report_Raw_Data[Reason for Closing] = "Duplicate Record")

            && NOT      ( Current_Report_Raw_Data[Opportunity Sale Type] ) = "Renewal"

    )

))

Any help would be VERY much appreciated!

Thanks,

Matt

[edhans · Accepted solution]

Yes, and No. 

Power Query cannot see what the user has selected, so ALLSELECTED() means nothing to it. Power Query is before data goes into the model, so no model filtering of any kind is not relevent here.

Aside from that, it seems you just have a rather tedious but not complex filter to set up. I created the sample data below as an example:

I then filtered it mostly using the UI, but some I edited the formula bar. For example, when I unselected DLS, Power Query thought I wanted it to = "ABC" so I had to change it manually to <> "DLS"

See the code below. If you need more help, post back with more details and some sample data using the guide in the links at the very bottom of this post. There is no "DAX to M" converter, so we'll just need to walk through what you need.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUYoAYkcnZ6VYHbgQELn4BCOLYFFEtD6EkKElRBFCG1gEi6II7KoIC8GMigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Opportunity = _t, Team = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Opportunity", type text}, {"Team", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Year] = 2020) and ([Opportunity] = "X") and ([Team] <> "DLS"))
in
    #"Filtered Rows"

 How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum