Forum Discussion

aw3w2's avatar
aw3w2
Regular Visitor
6 months ago
Solved

Apply data to all rows in a filtered column

Hello- fairly new to Power BI so I'm hoping this one is easy. I'm trying to classify each owner as Yes or NO based on whether or not they achieved a Tier 3 (the tiers go from 1-10, and achieving Tier...
Need Help
  • MarkLaf's avatar
    MarkLaf
    5 months ago

    You have a few different options.

     

    You can join the output to a reference of your original table. Steps would be: 1) finalize your original table, disable load; 2) reference your original from #1 and perform the grouping I outlined above, disable load; 3) reference your original from #1, join the categories from #2, enable load

     

    While this is relatively straightforward to do through the UI, it's not as performant as just doing the work inside the group. For that you'll have to implement with M in your formula bar or advanced editor. Here is the full advanced editor snip:

     

    let
    Source = Sample,
    GroupOps = Table.Group(Source, {"Owner"}, {{
        "GroupOps", each [
            grouped_rows = _, 
            group_category = if List.Max(grouped_rows[Tiers]) >= 3 then "Yes" else "No",
            grouped_rows_with_category = Table.AddColumn( 
                grouped_rows, "Threshold Met", 
                each group_category, type text 
            )
        ][grouped_rows_with_category], 
        type table
    }}),
    Combine = Table.Combine( GroupOps[GroupOps] )
in
    Combine

     

    Output:

     

     

MarkLaf's avatar
MarkLaf
Super User
6 months ago

If you are interested in a Power Query solution, the basic operation you are looking for is Group By...

 

Get the max of Tier to see all Owners who have at least one row of Tier >= 3. You can then add a custom column to do the if [Tier] >= 3 then "Yes" else "No" check or just modify the group by formula to do the same check ( if List.Max([Tier]) >= 3 then "Yes" else "No" )

 

Here is a quick demo doing the latter.

 

Using following data:

 

Sample

Tiers Owner
1 A
2 A
1 A
2 B
3 B
1 B
1 C
1 C
2 C
2 D
1 D
1 D
1 E
3 E

 

 

Edit: realized your tiers go up to 10, so updated comparison op from = to >=. It's still = in the gif but you get the idea

aw3w2's avatar
aw3w2
Regular Visitor
6 months ago

This is great but how do I get out of the grouping so this shows just as a new column in Power Query and ultimately my table?

  • MarkLaf's avatar
    MarkLaf
    Super User
    5 months ago

    You have a few different options.

     

    You can join the output to a reference of your original table. Steps would be: 1) finalize your original table, disable load; 2) reference your original from #1 and perform the grouping I outlined above, disable load; 3) reference your original from #1, join the categories from #2, enable load

     

    While this is relatively straightforward to do through the UI, it's not as performant as just doing the work inside the group. For that you'll have to implement with M in your formula bar or advanced editor. Here is the full advanced editor snip:

     

    let
    Source = Sample,
    GroupOps = Table.Group(Source, {"Owner"}, {{
        "GroupOps", each [
            grouped_rows = _, 
            group_category = if List.Max(grouped_rows[Tiers]) >= 3 then "Yes" else "No",
            grouped_rows_with_category = Table.AddColumn( 
                grouped_rows, "Threshold Met", 
                each group_category, type text 
            )
        ][grouped_rows_with_category], 
        type table
    }}),
    Combine = Table.Combine( GroupOps[GroupOps] )
in
    Combine

     

    Output: