Creating Tables in Power BI with Conditions get all columns where the conditions meet.

[Anonymous]

Hi All, 

I dont know if the best way to achieve this is DAX or Power Query but here is my scenario.

Create a new table in power bi from an existing table with all columns but only take the record where value of column A is maximum when column B and column C match.

[v-easonf-msft]

Hi, @Anonymous 

In PowerQuery:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUSpLzClNNUQwYnWilYwxxI3A4iYY4sZgcTMc5pgjtKOIGxpgSEAsMDTEkECzAV2DMUIdqhUmGBIQHZYY4saobkUWjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type text}, {"C", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"B", "C"}, {{"Max  each group", each List.Max([A]), type nullable number}}),
    #"Added Conditional Column" = Table.AddColumn(#"Grouped Rows", "flag", each if [B] = [C] then [Max  each group] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each [flag] <> null)
in
    #"Filtered Rows"

Calculated table:

Table 3 = 
var tab = 
FILTER(
    'Table',
    [B]=[C]&&
    [A]=
    CALCULATE(
        MAX('Table'[A]),
        FILTER(
            ALL('Table'),
            'Table'[B]=EARLIER('Table'[B])&&
            'Table'[C]=EARLIER('Table'[C])
        )
    )
)
return
tab

Pbix attached 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-easonf-msft ,

Thank you for your solution. I think my question was not very elaborate so I would like to make a calrification. The Column B and C are not to be matched with each other. Rather I want the data to be Grouped by them. I mean when i group my data by column B and C the there would be alot of groups and for each group i want to get the row where Column A value is the gratest. Hope this Clears my question.

[v-easonf-msft]

Hi , @Anonymous 

Do you have resolved it? If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-easonf-msft]

Hi, @Anonymous 

Have you tried this option"group by"?

If it doesn't  meet your requirement ,please share your sample data and expected result in excel.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.